VBA goalseek on many columns of data

[pingpong777]

hello. i have a corkscrew set up that stretches/repeats over 120 columns of data. i created a basic macro in VBA using the following syntax

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+g
'
Range("g68").GoalSeek Goal:=Range("g61"), ChangingCell:=Range("g63")
End Sub


the above solves for column G. being a corkscrew, when column G is solved, cell H61 changes, impacting the goalseek happening on column H, and the process repeats. in columns where the Row 61 value is 0, no calculation is necessary, but again, each value in Row 61 is dependent on on the calculations in the previous column.

what i would like to do is set up a single function that instructs excel to evaluate G61 and if G61>0, run the goalseek above, and if not, go to H61 and start again, all the way through column DV. it's important to go sequentially because eventually all remaining values in Row 61 will be 0, which is fine.

i had previously just copy pasted 120 lines to cover each column, but i have to repeat this process for several more 120-column corkscrews, and excel told me the procedure is too large. my hope is that i can cover each corkscrew with a single goalseek command with the logic described above, which will stay under excel's maximum procedure length.


all conversation and assistance are greatly appreciated. i'm sure this is a fairly quick fix, but i'm new to setting things up in VBA, so i'm not familiar with the ins and outs of the syntax and logic.


thanks,

pingpong777

 

[mhillmann]

I am not entirely sure if this is what you are looking for, (I have actually never worked with goalseek before) but I hope this gets you closer.

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+g
'
    For cols = 7 To ActiveSheet.UsedRange.Columns.count                                                   ' Starts at 7 since that is column "G". You can change that if needed.
        If Cells(61, cols) <> "" Then
            Cells(68, cols).GoalSeek Goal:=Cells(61, cols), ChangingCell:=Cells(63, cols)
        End If
    Next
    
End Sub

 

[pingpong777]

that's actually perfect! thank you!

on a related note: right now, there are quite a few identical corkscrews from top to bottom in the worksheet, each with the same structure, evenly spaced 21 rows apart. is there a way to make a relative reference to have this process repeat every 21 rows?

 

[mhillmann]

Yeah, just add another loop to go through the rows as well as the columns.

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+g
'
    Dim x As Integer
    x = 1                                                                                                                         ' Set x to the starting row value
    
    Do While x < ActiveSheet.usedrange.Rows.Count
        For Cols = 7 To ActiveSheet.usedrange.Columns.Count                                                   ' Starts at 7 since that is column "G". You can change that if needed.
            If Cells(x, Cols) <> "" Then
                Cells(x + 7, Cols).GoalSeek Goal:=Cells(x, Cols), ChangingCell:=Cells(x + 2, Cols)
            End If
        Next
        
        x = x + 21
    Loop
    
End Sub

 

[pingpong777]

Thanks again, this is extremely helpful