Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop How-To
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop How-To
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 "Karin" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop How-To
Thank you. I will play with this and see what happens.
"Chip Pearson" wrote: 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 "Karin" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop How-To
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. "Chip Pearson" wrote: 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 "Karin" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Naming Worksheets - Loop within a loop issue | Excel Programming | |||
Naming Worksheets - Loop within a loop issue | Excel Programming | |||
(Complex) Loop within loop to create worksheets | Excel Programming | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
Problem adding charts using Do-Loop Until loop | Excel Programming |