Loop How-To

[Guest]

Hi, Total newbie here, but I'm learning. I have a workbook that already has
some code in it. I'm trying to modify it. This is what's there now:

Set ws = Sheets("Summary")

CltID = ws.Range("a5")
BegEng = ws.Range("b5")
EndEng = ws.Range("c5")
BegWip = ws.Range("d5")
EndWip = ws.Range("e5")

And then there's more code that gets data from sql connection. What I would
like to do is get the data for range A5 through E20 (we can name the range
"ClientInfo"). Can anyone tell me how to modify the above so that it loops
through a range and pulls in the data for each line?
TIA

 

[Chip Pearson]

Karin,

Try something like


Dim FirstRow As Long
Dim LastRow As Long
Dim RowNdx As Long
Dim WS As Worksheet

Set WS = Worksheets("Summary")

With Range("ClientInfo")
FirstRow = .Cells(1, 1).Row
LastRow = .Cells(.Cells.Count).Row
End With

With WS
For RowNdx = FirstRow To LastRow
cltid = .Cells(RowNdx, "A")
BegEng = .Cells(RowNdx, "B")
EndEng = .Cells(RowNdx, "C")
BegWip = .Cells(RowNdx, "D")
EndWip = .Cells(RowNdx, "E")
''''''''''''''''''''''''''''''''''''''
' your code here
''''''''''''''''''''''''''''''''''''''
Next RowNdx

 

[Guest]

Thank you. I will play with this and see what happens.

 

[Guest]

I didn't give enough information, sorry. After the code that creates a sql
query, there is this :

ws.Range("AuditActFee").Value = txtAuditTotalDollars
ws.Range("AuditTotalHours").Value = txtAuditTotalHours

(Which is telling it where to put the data it found - it's named cells "g5"
and "g6", respectively.) So do I need to create a name for each cell I want
the data to go in through the loop or is there a better way to do that part.