Do until last row

  • Thread starter Thread starter bpotter
  • Start date Start date
B

bpotter

I am trying to write a macro that starts on cell D1 then goes until
the last row. I want to search for the value from column D in another
in another workbook and copy a corresponding cell into the first
workbook. I am mainly having problems with the do until loop that goes
down and does the search. I probably have around 40 names that I am
searching for but by the time we get done using the report in a few
months I will have close to 200. Any help would be greatly
appreciated.
 
It is usually quicker to define the range early in the code (Early Binding)
then you can use a For Each loop to perform operations. You can try something
like the following:

Sub Test()
Dim sAddr As String
Dim rngData As Range
Dim c

Set rngData = Range("A2:" &
Selection.SpecialCells(xlCellTypeLastCell).Address)
rngData.Select

For Each c In rngData
'your code here
Next c

End Sub

There is no need to select the range - this is just for testing - remove
this when the code is complete.

As Don said seeing your code enables more complete replies.

Regards
Peter
 
You can use following code to copy values in column B from one
workbook to another.
1.First add a command button from the control box and paste following
codes in the code window.
2.Open both workbooks and run the macro code.
3.Click on the button and check the result

Code: 
Private Sub CommandButton1_Click()
Dim row As Integer, col As Integer
row = 1
col = 4


While Workbooks(1).Sheets(1).Cells(row, col).Value <> ""
Workbooks(2).Sheets(1).Cells(row, col).Value = Workbooks
(1).Sheets(1).Cells(row, col).Value
row = row + 1
Wend

End Sub

Chris
 
really shouldn't dim the row variable as an integer. it's only valid to 32,767.
use long, it's valid to 2,147,483,647.

granted, in this case it may work, but one never knows how many rows will be
addressed. even excel 2003 has over 65000 rows while over a million are
available in excel 2007.
 
Back
Top