VBA Looping with Variable columns

[kmowers07]

I am trying to loop thru a range of cells and place a formula into each cell that has absolute and relevant values in it. the Starting range is $V$23:$V$147 to the last column.
In order to find the last column i have to find the number of days between 2 dates from another sheet.

i am finding looping a mystery as much as i have read tutorials and classes on it.

The formula that needs to go in each cell is

=IF($N23>V$22,"",
IF(AND(V$22>TODAY(),V179="T"),SUMIFS(Agent_HC_Summary!$S:$S,Agent_HC_Summary!$B:$B,GSM_Daily_Summary!$D23,Agent_HC_Summary!$C:$C,GSM_Daily_Summary!V$22)-(SUMIFS(Agent_HC_Summary!$S:$S,Agent_HC_Summary!$B:$B,GSM_Daily_Summary!$D23,Agent_HC_Summary!$C:$C,GSM_Daily_Summary!V$22)*XLOOKUP(V$22,Agent_HC_Summary!$C:$C,Agent_HC_Summary!$O:$O)),
IF(AND(V$22>TODAY(),V179="N"),SUMIFS(Agent_HC_Summary!$S:$S,Agent_HC_Summary!$B:$B,GSM_Daily_Summary!$D23,Agent_HC_Summary!$C:$C,GSM_Daily_Summary!V$22)-(SUMIFS(Agent_HC_Summary!$S:$S,Agent_HC_Summary!$B:$B,GSM_Daily_Summary!$D23,Agent_HC_Summary!$C:$C,GSM_Daily_Summary!V$22)*XLOOKUP(V$22,Agent_HC_Summary!$C:$C,Agent_HC_Summary!$N:$N)),
IF(AND(V$22>TODAY(),V179="P"),SUMIFS(Agent_HC_Summary!$S:$S,Agent_HC_Summary!$B:$B,GSM_Daily_Summary!$D23,Agent_HC_Summary!$C:$C,GSM_Daily_Summary!V$22)-(SUMIFS(Agent_HC_Summary!$S:$S,Agent_HC_Summary!$B:$B,GSM_Daily_Summary!$D23,Agent_HC_Summary!$C:$C,GSM_Daily_Summary!V$22)*XLOOKUP(V$22,Agent_HC_Summary!$C:$C,Agent_HC_Summary!$M:$M)),
SUMIFS(Agent_HC_Summary!$S:$S,Agent_HC_Summary!$B:$B,GSM_Daily_Summary!$D23,Agent_HC_Summary!$C:$C,GSM_Daily_Summary!V$22)))))

Please note though the values in the formula will change based on what cell its in

can someone please assist me or tell me where to start

Sub GSM_Daily_Sub()


Dim wb As Workbook, ws As Worksheet, datews As Worksheet, lstcol As Long, lstr As Long, j As Long, i As Long



  Set ws = ThisWorkbook.Sheets("GSM_Daily_Summary")
  Set datews = ThisWorkbook.Sheets("Agent_HC_Input")
  
'finding out the amount of days between the 2 Dates on Agent HC Input sheet
  Difference_Format = "d"
Date1 = CDate(datews.Range("M2"))
Date2 = CDate(datews.Range("N2"))

'sets the last column equal to the amount of days between the 2 dates entered

lstcol = DateDiff(Difference_Format, Date1, Date2)
'set last row variable to row 147
lstr = 147
    
    ws.Activate
    


' to loop through each column and then go to the nextr row
  For i = 23 To lstr  ' this starts on row 23
   For j = 22 To lstcol  ' this starts on column 22 which is column V








   Next j
 Next i
 
 
End Sub

 

[GSP75]

Here's an example of how you could loop through the range of cells in column V and place the formula you provided into each cell that has absolute and relevant values in it:
Sub GSM_Daily_Sub()

Dim ws As Worksheet, datews As Worksheet
Dim lstcol As Long, lstr As Long, j As Long, i As Long

Set ws = ThisWorkbook.Sheets("GSM_Daily_Summary")
Set datews = ThisWorkbook.Sheets("Agent_HC_Input")

'finding out the amount of days between the 2 Dates on Agent HC Input sheet
Dim Difference_Format As String
Difference_Format = "d"
Dim Date1 As Date
Dim Date2 As Date
Date1 = CDate(datews.Range("M2"))
Date2 = CDate(datews.Range("N2"))

'sets the last column equal to the amount of days between the 2 dates entered
lstcol = DateDiff(Difference_Format, Date1, Date2)
'set last row variable to row 147
lstr = 147

ws.Activate

' to loop through each column and then go to the next row
For i = 23 To lstr ' this starts on row 23
For j = 22 To lstcol ' this starts on column 22 which is column V
If ws.Cells(i, j).Value <> "" Then 'check if cell has a value
ws.Cells(i, j).Formula = "=IF($N" & i & ">V$22,"""",IF(AND(V$22>TODAY(),V" & i & "=""T""),SUMIFS(Agent_HC_Summary!$S:$S,Agent_HC_Summary!$B:$B,GSM_Daily_Summary!$D" & i & ",Agent_HC_Summary!$C:$C,GSM_Daily_Summary!V$22)-(SUMIFS(Agent_HC_Summary!$S:$S,Agent_HC_Summary!$B:$B,GSM_Daily_Summary!$D" & i & ",Agent_HC_Summary!$C:$C,GSM_Daily_Summary!V$22)*XLOOKUP(V$22,Agent_HC_Summary!$C:$C,Agent_HC_Summary!$O:$O)),IF(AND(V$22>TODAY(),V" & i & "=""N""),SUMIFS(Agent_HC_Summary!$S:$S,Agent_HC_Summary!$B:$B,GSM_Daily_Summary!$D" & i & ",Agent_HC_Summary!$C:$C,GSM_Daily_Summary!V$22)-(SUMIFS(Agent_HC_Summary!$S:$S,Agent_HC_Summary!$B:$B,GSM_Daily_Summary!$D" & i & ",Agent_HC_Summary!$C:$C,GSM_Daily_Summary!V$22)*XLOOKUP(V$22,Agent_HC_Summary!$C:$C,Agent_HC_Summary!$N:$N)),IF(AND(V$22>TODAY(),V" & i & "=""P""),SUMIFS(Agent_HC_Summary!$S:$S,Agent_HC_Summary!$B:$B,GSM_Daily_Summary!$D" & i & ",Agent_HC_Summary!$C:$C,GSM_Daily_Summary!V$22)-(SUMIFS(Agent_HC_Sum

 

[kmowers07]

that formula will not work because the V179 adjusts down the page it doesn't start at the same row we are looping thru There is some data below row 147 that we are pulling into that formula. the set of data for V179 goes to V304
as you move down a row from row 23, everything in that formula shifts down 1 so V179 would be V180 ect.

 

[GSP75]

I see. In that case, you can modify the formula to use a dynamic reference for the row number in column V. You can do this by replacing the absolute reference to row 22 with a relative reference to the row of the cell being evaluated in the loop.

Here's an example of how you could modify the formula to use a dynamic reference: ws.Cells(i, j).Formula = "=IF($N" & i & ">V" & j & ","""",IF(AND(V" & j & ">TODAY(),V" & i & "=""T""),SUMIFS(Agent_HC_Summary!$S:$S,Agent_HC_Summary!$B:$B,GSM_Daily_Summary!$D" & i & ",Agent_HC_Summary!$C:$C,GSM_Daily_Summary!V" & j & ")-(SUMIFS(Agent_HC_Summary!$S:$S,Agent_HC_Summary!$B:$B,GSM_Daily_Summary!$D" & i & ",Agent_HC_Summary!$C:$C,GSM_Daily_Summary!V" & j & ")*XLOOKUP(V" & j & ",Agent_HC_Summary!$C:$C,Agent_HC_Summary!$O:$O)),IF(AND(V" & j & ">TODAY(),V" & i & "=""N""),SUMIFS(Agent_HC_Summary!$S:$S,Agent_HC_Summary!$B:$B,GSM_Daily_Summary!$D" & i & ",Agent_HC_Summary!$C:$C,GSM_Daily_Summary!V" & j & ")-(SUMIFS(Agent_HC_Summary!$S:$S,Agent_HC_Summary!$B:$B,GSM_Daily_Summary!$D" & i & ",Agent_HC_Summary!$C:$C,GSM_Daily_Summary!V" & j & ")*XLOOKUP(V" & j & ",Agent_HC_Summary!$C:$C,Agent_HC_Summary!$N:$N)),IF(AND(V" & j & ">TODAY(),V" & i & "=""P""),SUMIFS(Agent_HC_Summary!$S:$S,Agent_HC_Summary!$B:$B,GSM_Daily_Summary!$D" & i & ",Agent_HC_Summary!$C:$C,GSM_Daily_Summary!V" & j & ")-(SUMIFS(Agent_HC_Summary!$S:$S,Agent_HC_Summary!$B:$B,GSM_Daily_Summary!$D" & i & ",Agent_HC_Summary!$C:$C,GSM_Daily_Summary!V" & j & ")*XLOOKUP(V" & j & ",Agent_HC_Summary!$C:$C,Agent_HC_Summary!$M:$M)),0)))"

In this modified formula, the reference to V$22 has been replaced with V" & j & ", which will use the row number of the cell being evaluated in the loop. This should allow the formula to correctly reference the cells you need even if the number of rows changes.

 

[Fluff]

When posting code please use code tags How to Post Your VBA Code
It helps to make it readable & also easier to copy.

 

[kmowers07]

I ended up changing and going with a dynamic range as the string substitution in the formula is confusing me. Thanks for the assistance

Sub GSM_Daily()


Dim wb As Workbook, ws As Worksheet, datews As Worksheet, lstcol As Long, GetCurrentColumn As String, gsmws As Worksheet

    Set gsmws = ThisWorkbook.Sheets("GSM_Daily_Summary")
    Set datews = ThisWorkbook.Sheets("Agent_HC_Input")
     
  
Call Clear_GSM
'finding out the amount of days between the 2 Dates on Agent HC Input sheet
  Difference_Format = "d"
Date1 = CDate(datews.Range("M2"))
Date2 = CDate(datews.Range("N2"))

'sets the last column equal to the amount of days between the 2 dates entered and offset the first 22 columns

lstcol = DateDiff(Difference_Format, Date1, Date2) + 22

'activate the GSM Daily Summary Page Worksheet

    gsmws.Activate
    'get the column letter for date difference
GetCurrentColumn = Split((Columns(lstcol).Address(, 0)), ":")(0)

'copies the formula into a dyanmic range

Range("V23:" & GetCurrentColumn & "147").Formula = "=IF($N23>V$22,""""," _
& "IF(AND(V$22>TODAY(),V179=""T""),SUMIFS(Agent_HC_Summary!$S:$S,Agent_HC_Summary!$B:$B,GSM_Daily_Summary!$D23,Agent_HC_Summary!$C:$C,GSM_Daily_Summary!V$22)-(SUMIFS(Agent_HC_Summary!$S:$S,Agent_HC_Summary!$B:$B,GSM_Daily_Summary!$D23,Agent_HC_Summary!$C:$C,GSM_Daily_Summary!V$22)*XLOOKUP(V$22,Agent_HC_Summary!$C:$C,Agent_HC_Summary!$O:$O))," _
& "IF(AND(V$22>TODAY(),V179=""N""),SUMIFS(Agent_HC_Summary!$S:$S,Agent_HC_Summary!$B:$B,GSM_Daily_Summary!$D23,Agent_HC_Summary!$C:$C,GSM_Daily_Summary!V$22)-(SUMIFS(Agent_HC_Summary!$S:$S,Agent_HC_Summary!$B:$B,GSM_Daily_Summary!$D23,Agent_HC_Summary!$C:$C,GSM_Daily_Summary!V$22)*XLOOKUP(V$22,Agent_HC_Summary!$C:$C,Agent_HC_Summary!$N:$N))," _
& "IF(AND(V$22>TODAY(),V179=""P""),SUMIFS(Agent_HC_Summary!$S:$S,Agent_HC_Summary!$B:$B,GSM_Daily_Summary!$D23,Agent_HC_Summary!$C:$C,GSM_Daily_Summary!V$22)-(SUMIFS(Agent_HC_Summary!$S:$S,Agent_HC_Summary!$B:$B,GSM_Daily_Summary!$D23,Agent_HC_Summary!$C:$C,GSM_Daily_Summary!V$22)*XLOOKUP(V$22,Agent_HC_Summary!$C:$C,Agent_HC_Summary!$M:$M))," _
& "SUMIFS(Agent_HC_Summary!$S:$S,Agent_HC_Summary!$B:$B,GSM_Daily_Summary!$D23,Agent_HC_Summary!$C:$C,GSM_Daily_Summary!V$22)))))"

'copies the range and pastes the values in
Range("V23:" & GetCurrentColumn & "147").Copy
Range("V23:" & GetCurrentColumn & "147").PasteSpecial Paste:=xlPasteValues
Range("A1").Select

gsmws.Activate
 
MsgBox ("Update to GSM Daily Summary Sheet Complete, file is now ready")
End Sub