Prev: Find if value does not exist in range.
Next: Select specific worksheets & copy - code problem
From: XKruodo on 11 Mar 2010 00:53
A sheet contains AC NO, DATE, and Amount of Clients.

116530 17-Feb-10 2000.00
39361 17-Feb-10 300.00
40236 18-Feb-10 350.00
10563 18-Feb-10 100.00
116530 19-Feb-10 250.00

Ac Nos are names of the excel workbook (116530.xls) located at D:\conference\

AC No Workbook looks like this. A Date, B Time, C Country, D Rate, E
Minutes, F Total, G is blank for remarks, H is date and I is Amount.


3-Mar-10 7:37 PM UK 1.00 58 58.00 2-Mar-10 200.00
3-Mar-10 8:46 PM UK 1.00 57 57.00
3-Mar-10 9:30 PM UK 1.00 25 25.00 4-Mar-10 500.00

1. I have to find first empty cell in column A ( A4 here )
2. Then i have to move cursor to H4 to paste data.
3. But if H4 is not empty i need to paste it to H5.

I tried this..

'Position the cursor in the first cell in the client file. Assumes
'client data will be posted beginning in column A. Adjust as required.
Range("A5").Select

' To Find the first empty cell where the current data can be posted.
Do While ActiveCell.Text > ""
ActiveCell.Offset(1, 0).Select
'Assumes Excel sheets have 65536 rows. This prevents running
'off the bottom of the worksheet and causing an error. Adjust as
'required.
If ActiveCell.Row > 65536 Then Exit Sub
Loop

'Paste the client data into the client worksheet file.
ActiveSheet.Paste

What do i need to change here so that i can paste data to empty cell in
column H (Data in H is not contiguous )





From: Paul Robinson on 11 Mar 2010 09:11
On Mar 11, 5:53 am, XKruodo <XKru...(a)discussions.microsoft.com> wrote:
> A sheet contains AC NO, DATE, and Amount of Clients.
>
> 116530  17-Feb-10       2000.00
> 39361   17-Feb-10       300.00
> 40236   18-Feb-10       350.00
> 10563   18-Feb-10       100.00
> 116530  19-Feb-10       250.00
>
> Ac Nos are names of the excel workbook (116530.xls)  located at D:\conference\
>
> AC No Workbook looks like this. A Date, B Time, C Country, D Rate, E
> Minutes, F Total, G is blank for remarks, H is date and I is Amount.
>
> 3-Mar-10        7:37 PM UK      1.00    58      58.00           2-Mar-10       200.00
> 3-Mar-10        8:46 PM UK      1.00    57      57.00                  
> 3-Mar-10        9:30 PM UK      1.00    25      25.00           4-Mar-10        500.00
>
> 1. I have to find first empty cell in column A ( A4 here )
> 2. Then i have to move cursor to H4 to paste data.
> 3. But if H4 is not empty i need to paste it to H5.
>
> I tried this..
>
> 'Position the cursor in the first cell in the client file. Assumes
> 'client data will be posted beginning in column A. Adjust as required.
> Range("A5").Select
>
> ' To Find the first empty cell where the current data can be posted.
> Do While ActiveCell.Text > ""
>   ActiveCell.Offset(1, 0).Select
>     'Assumes Excel sheets have 65536 rows. This prevents running
>     'off the bottom of the worksheet and causing an error. Adjust as
> 'required.
>     If ActiveCell.Row > 65536 Then Exit Sub
> Loop
>
> 'Paste the client data into the client worksheet file.
> ActiveSheet.Paste
>
> What do i need to change here so that i can paste data to empty cell in
> column H (Data in H is not contiguous )

With Range("A1").End(xlDown).offset(1,0)
From: Paul Robinson on 11 Mar 2010 09:23
Hi
This does your 3 steps

With Worksheets("Client File").Range("A1").End(xlDown).offset(1,0)
'one below end
If Cstr(.offset(0,6).Value)="" then
Something?.Copy .offset(0,6) '6 to right i.e H
Else
Something?.Copy .offset(1,6) '1 below that
end if
End with

I don't know what it is you are copying, so I have left it at
Something? It might be

Workbooks("Master").Worksheets("Mastersheet").Range"B6")

for example

regards
Paul

On Mar 11, 5:53 am, XKruodo <XKru...(a)discussions.microsoft.com> wrote:
> A sheet contains AC NO, DATE, and Amount of Clients.
>
> 116530  17-Feb-10       2000.00
> 39361   17-Feb-10       300.00
> 40236   18-Feb-10       350.00
> 10563   18-Feb-10       100.00
> 116530  19-Feb-10       250.00
>
> Ac Nos are names of the excel workbook (116530.xls)  located at D:\conference\
>
> AC No Workbook looks like this. A Date, B Time, C Country, D Rate, E
> Minutes, F Total, G is blank for remarks, H is date and I is Amount.
>
> 3-Mar-10        7:37 PM UK      1.00    58      58.00           2-Mar-10       200.00
> 3-Mar-10        8:46 PM UK      1.00    57      57.00                  
> 3-Mar-10        9:30 PM UK      1.00    25      25.00           4-Mar-10        500.00
>
> 1. I have to find first empty cell in column A ( A4 here )
> 2. Then i have to move cursor to H4 to paste data.
> 3. But if H4 is not empty i need to paste it to H5.
>
> I tried this..
>
> 'Position the cursor in the first cell in the client file. Assumes
> 'client data will be posted beginning in column A. Adjust as required.
> Range("A5").Select
>
> ' To Find the first empty cell where the current data can be posted.
> Do While ActiveCell.Text > ""
>   ActiveCell.Offset(1, 0).Select
>     'Assumes Excel sheets have 65536 rows. This prevents running
>     'off the bottom of the worksheet and causing an error. Adjust as
> 'required.
>     If ActiveCell.Row > 65536 Then Exit Sub
> Loop
>
> 'Paste the client data into the client worksheet file.
> ActiveSheet.Paste
>
> What do i need to change here so that i can paste data to empty cell in
> column H (Data in H is not contiguous )

With Worksheets("Client File").Range("A1").End(xlDown).offset(1,0)
If Cstr(.offset(0,6).Value)="" then
.Paste .offset(0,6)
Else
.Paste .offset(1,6)
end if
End with
 | 
Pages: 1
Prev: Find if value does not exist in range.
Next: Select specific worksheets & copy - code problem