Looping through rows then columns

Hi I'm trying to create a macro to help with my data.

I have a column "Rankw" on sheet "Data" that has values ranging from -1711 to 1815 ish.
On another sheet "Workings" i want to populate cells Min (B3) and Max (B4) with the values from column "Rankw". By changing the values of Min and Max i change the range of values my sumif formula uses to give me a value in H4 on "Workings"

I'd like to copy the value of H4 for each instance of changing Min and Max to a sheet called "Data2"

So my results would be something like:

Min -1711 Max 1815 H4= 0
Min -1711 Max 1669 H4= 3
Min -1711 Max 1618 H4= 3
.
.
.
.
.

.
Min 1815 Max 1618 H4= 1
Min 1815 Max 1669 H4= 1
Min 1815 Max 1815 H4= 2

For ease so far i have started by creating a grid with the values from column "Rankw" from -1711 to 1815 in column A on "Data2" and values -1711 to 1815 in row 1.

I've tried using solver using a loop that runs through the first column going down row by row and pasting the value of H4 into the cells B2:B806, but how do i get the loop to go onto the next column and starting again in row 2, then repeating until upto column ADZ (805 columns in total)?

Sub Macro3()
'
' Run through each row and column run the solver and paste value in grid on Data2 sheet
'

ScreenUpdating = False

'   Loop Solver through rows

Dim i As Integer

For i = 0 To 804
    
    SolverReset
    
'   Set start row for Max Rankw. $K$2 is the start of the list i have pasted of values from the "Rankw" column

    SolverAdd CellRef:="$B$4", Relation:=2, FormulaText:=Range("$k$2").Offset(i, 0).Address
    SolverOk SetCell:="$H$4", MaxMinVal:=1, ValueOf:="0", ByChange:="$B$4"
    SolverSolve (True)
    
    SolverSolve userfinish:=True
    SolverFinish KeepFinal:=1
    
 '  Paste data to Data2 sheet
 
    Sheets("Data2").Range("$b$2").Offset(i, 0).Value = Sheets("Workings").Range("$h$4").Value
  
      
Next i

ScreenUpdating = True

    
End Sub

Not sure I understands your problem properly but it seems to me that this is what you wish to paste with an row and column offset.

Sheets("Data2").Range("$b$2").Offset(i, 0).Value = Sheets("Workings").Range("$h$4").Value

Then you need two "For" loops

Dim j As Integer
Dim i As Integer

For j = 0 To 805
For i = 0 To 804

' Your code goes 
' in here

Sheets("Data2").Range("$b$2").Offset(i, j).Value = Sheets("Workings").Range("$h$4").Value

Next i

Next j

' rest of you code goes here

First j = 0 , then macro loops through all i values from i = 0 to i 804, it then moves down to "Next j" and changes j to 1, loops though all i values, increases j to 2 and so forth.

Alf

Thanks Alf,

I didn't see your post but that's exactly what i did, and got rid of the solver code as i was making it more complicated than it needed to be.

Sub Macro4()

ScreenUpdating = False

Dim ws As Worksheet, rng As Range, i As Integer, j As Integer

    Set ws = Worksheets("Workings")   ' ws with list of Rankw values
    Set rng = ws.Range("k2") ' 1st row with Rankw value
    Set rng2 = ws.Range("k2")  '

For j = 0 To 805

    Range("minrankw") = rng2

For i = 0 To 805

    Range("maxrankw") = rng
    Sheets("Data2").Range("$b$2").Offset(i, j).Value = Sheets("Workings").Range("$h$4").Value


    Set rng = rng.Offset(1, 0) ' select next ticker by stepping down 1 row

Next i

    Set rng2 = rng2.Offset(1, 0)
    Set rng = ws.Range("k2")
        
Next j
        
End Sub

if can please provide a sample file. (remove any sensitive data)

get advance > manage attachment > upload file > close window