Excel Do Loop question

[vzachin]

I'm new to VBA. I have an Excel sheet with data in column A. The data can be one or more row. How do I copy A1, then paste that data into different application or worksheet, then copy A2, paste and so forth until I reach a blank row and then stop? I know I need a DO Loop but I'm not sure how to write this. I was not successful in finding the answer in the archives or FAQs.
thanks

 

[PHV]

A basic startic point:
r = 1
Do While Cells(r, 1).Value > " "
MsgBox "A" & r & "=" & Cells(r, 1).Value
Loop

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

OOps, hit Submit too fast :~/
A basic startic point:
r = 1
Do While Cells(r, 1).Value > " "
MsgBox "A" & r & "=" & Cells(r, 1).Value
r = r + 1
Loop

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[vzachin]

thanks PHV,
but I don't understand how do I copy each cell?
for example:
Range("A1").Select
Selection.Copy
Range("A2").Select
Selection.Copy

And how would I paste A1 to D1,A2 to D2 and so forth?

thanks again

 

[vzachin]

PHV,
how do I get out of this Loop? I want the Loop to Stop when I reach the blank row.

thanks again

 

[GlennUK]

hi vzachin,

can you tell exactly what you are aiming for, functionality-wise? Doing processing of one cell at a time might be very inefficient.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[vzachin]

hi GlennUK,

I'm receiving a file in excel form with columns. There will be data in A1,A2,A3 etc.
I need to imput this data into a mainframe database. Originally,I've been copying & pasting from Excel to the mainframe manually.
I know this can be done with VBA but I don't know how to write the code to stop copying when it reaches a blank row. The blank row can be in A2 or A#### but should not exceed 10000.
I think I need an IF and DO Loop statement somehow.
It's definitly very inefficient & tedious with my method.

thanks for your reply