This a macro that I recorded then added on to solve iteratively for the value of Range("G4") that causes the calculated Range("F4") to be zero:
' Macro recorded 11/13/2005 by Leon F Michon
'
' Keyboard Shortcut: Ctrl+b
'
    Range("G4").Select
    Range("G4").GoalSeek Goal:=0, ChangingCell:=Range("F4")
    Range("G5").Select
    Range("G5").GoalSeek Goal:=0, ChangingCell:=Range("F5")
    Range("G6").Select
    Range("G6").GoalSeek Goal:=0, ChangingCell:=Range("F6")
    Range("G7").Select
    Range("G7").GoalSeek Goal:=0, ChangingCell:=Range("F7")
    Range("G8").Select
    Range("G8").GoalSeek Goal:=0, ChangingCell:=Range("F8")
    Range("G9").Select
    Range("G9").GoalSeek Goal:=0, ChangingCell:=Range("F9")
    Range("G10").Select
    Range("G10").GoalSeek Goal:=0, ChangingCell:=Range("F10")
    Range("G11").Select
    Range("G11").GoalSeek Goal:=0, ChangingCell:=Range("F11")
I copied the original macro seven times and changed the row in each line to goal solve for rows 4 through 10. It's kind of like using a sledge hammer to drive a tack.

I'm thinking that there has to be a similar construct to the "do" loop in FORTRAN. You would set up a For, Next loop:

     Dim i As Integer
     For i = 4,10
     Range("G, i").Select
     Range("G, i").GoalSeek Goal:=0, ChangingCell:=Range("F,i")
     Next i
     End Sub

    How do I make the indexing variable for the loop to be the row address for Range ("Gi") and Range ("Fi"). Please forgive my speaking VBA with a FORTRAN accent; I cut my teeth on FORTRAN back in the early 60's.

dim i as long
for i = 4 to 11
range("R" & i).select
range("R" & i).goalseek ...
next

Basically ("R" & i) appends the value i (as a string) to the string "R"

Marco

I got your macro working great using this code:

Sub Macro7()
    Dim i As Long
     For i = 4 To 11
     Range("G" & i).Select
     Range("G" & i).GoalSeek Goal:=0, ChangingCell:=Range("F" & i)
     Next i
     End Sub

I am new at this and this is probably stupid question. But how would I make this macro apply across a row and not down a column. IE make it solve G4, H4, I4, J4, K4 by changing G5, H5, etc..

I have tried many things, but nothing works. I am so new

ryanfj;

keep the 4 constant... and loop the ascii of the chars (like G H...)

for s = chr(65) to chr(70) step 1
 Range(s & "4").select.... (its contains s="A", so its Range(A4).select... and Range(B4).select... and so on)
 ...
next

I used the same set of logic in one my module exporting the data of grid to excel sheet.

hope this helps.

With Regards,
Raghavendra Mudugal

Dear Raghavendra,

Thank you very much for your help. What you are saying definitely makes sense. However, I cannot get it to work, I am sure it is 100% my fault since I am new. I am sure there is some dumb mistake in my code. Do you see where the problem is?

I tried to seek a goal on row 1 by changing row two.

Sub Macro7()
    Dim s As Long
     For s = Chr(65) To Chr(70) Step 1
     Range(s & "1").Select
     Range(s & "1").GoalSeek Goal:=0, ChangingCell:=Range(s & "2")
     Next s
     End Sub

Thanks,
Ryan

ryanfj;

that functions chr(65) returns a string "A", so...
change that "Dim s As Long" to "Dim s as String"

Basically there are two functions Chr and Asc
Chr -> returns the char of the specified ASCII
Asc -> returns the int of the specified char

hope this should fix the problem.

With Regards,
Raghavendra Mudugal

Thanks again for your help.

I tried to do Dim s As String, but I get a type mismatch error. Any suggestions?

Sub Macro7()
    Dim s As String
     For s = Chr(65) To Chr(70) Step 1
     Range(s & "1").Select
     Range(s & "1").GoalSeek Goal:=0, ChangingCell:=Range(s & "2")
     Next s
End Sub

Okay, I got frustrated and called my brother to help me out. This is what he did and it seems to work. Maybe this will help some one who is trying to do the same thing as me.

Thanks for your help again.



Sub goalseek()
   Dim S As String
   Dim incr As Long
   incr = 64

   Do
    incr = incr + 1
    S = Chr$(incr)
    Range(S & "1").Select
    Range(S & "1").goalseek Goal:=0, ChangingCell:=Range(S & "2")
   Loop Until incr = 83


End Sub

It supposed to this....

dim i as integer
dim s as string
for i = 65 to 70 step 1
 s = chr(i)
 Range(s & "1").Select
next

Actually I shoud you a sample of iterating through the chars, and you copied the same line and doing it... so to the "i" we need to get the char of it.. try using this code, it will work... fine and you dont have to increment the ascii value manually there, this for loop will that care of it.

hope this helps.

With Regards,
Raghavendra Mudugal