Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
VB How-To Ask your "How do I do this with VB?" questions in this forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB How-To section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 13th, 2005, 10:54 PM
Registered User
 
Join Date: Nov 2005
Location: Gilbert, , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default "DO" loop

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.

 
Old November 14th, 2005, 02:43 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Alameda, ca, USA.
Posts: 627
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old June 5th, 2007, 04:53 PM
Registered User
 
Join Date: Jun 2007
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



 
Old June 6th, 2007, 04:40 AM
Friend of Wrox
Points: 687, Level: 9
Points: 687, Level: 9 Points: 687, Level: 9 Points: 687, Level: 9
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2004
Location: Chennai, Tamilnadu, India.
Posts: 221
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old June 6th, 2007, 11:14 AM
Registered User
 
Join Date: Jun 2007
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old June 6th, 2007, 10:46 PM
Friend of Wrox
Points: 687, Level: 9
Points: 687, Level: 9 Points: 687, Level: 9 Points: 687, Level: 9
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2004
Location: Chennai, Tamilnadu, India.
Posts: 221
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old June 7th, 2007, 03:16 PM
Registered User
 
Join Date: Jun 2007
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


 
Old June 7th, 2007, 03:57 PM
Registered User
 
Join Date: Jun 2007
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old June 8th, 2007, 12:54 AM
Friend of Wrox
Points: 687, Level: 9
Points: 687, Level: 9 Points: 687, Level: 9 Points: 687, Level: 9
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2004
Location: Chennai, Tamilnadu, India.
Posts: 221
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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




Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with for-each loop athanatos XSLT 0 April 10th, 2006 07:20 PM
Do Until loop with IF crmpicco Classic ASP Databases 2 June 15th, 2005 05:35 PM
For....Loop kliu9 Excel VBA 5 February 10th, 2005 06:43 AM
Do Loop junemo Beginning PHP 8 July 28th, 2004 02:58 AM
nested while loop doesn't loop hosefo81 PHP Databases 5 November 12th, 2003 08:46 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.