Varying a macro based on a cell's value

[Guest]

I'm just getting into macros and VB in excel. Don't really know VB but am
picking it up as I go along. Am familiar with some other programming
languages.

Question 1:
Please help with a 'simple' (?) way to start a macro based on the value in a
cell.

Example: In a macro that is looking at column G, starting from row 1 and
going down, as soon as the letter "z" is found in the first position, I want
to execute another macro.

Question 2: What is a way (or more if there is more than one way) to get a
cell's value, into a macro to vary the performance of that macro?

Example: In a specfic cell whose reference I know, say r1c1, will be a
number, say 40. In a macro, I want to do something 40 times.

Thanks so much, Neal Z

 

[Tim Williams]

I'm just getting into macros and VB in excel. Don't really know VB
but am
picking it up as I go along. Am familiar with some other programming
languages.

Question 1:
Please help with a 'simple' (?) way to start a macro based on the
value in a
cell.

Example: In a macro that is looking at column G, starting from row 1
and
going down, as soon as the letter "z" is found in the first
position, I want
to execute another macro.

dim r as range

set r=thisworkbook.sheets("sheetname").range("G1")

do while r.value<>""
if r.value like "z*" then
'call your code here
end if
set r=r.offset(1,0)
loop

Question 2: What is a way (or more if there is more than one way) to
get a
cell's value, into a macro to vary the performance of that macro?

Example: In a specfic cell whose reference I know, say r1c1, will
be a
number, say 40. In a macro, I want to do something 40 times.

dim z as long, x as long

z=thisworkbook.sheets("sheetname").range("A1").value
for x=1 to z
'do your thing
next z

Hope this helps,
Tim.

 

[Guest]

Option Explicit
Sub Test_1()
MsgBox Answer_1("z", Range("G:G"))
End Sub
Function Answer_1(what As String, where As Range) As String
Dim found As Range
Set found = where.Find(what)
If found Is Nothing Then
Answer_1 = "not found"
Else
Answer_1 = found.Address
End If
End Function

Patrick Molloy
Microsoft Excel MVP
Sub Answer_2()
Dim index As Long
If IsNumeric(Range("A1")) Then
For index = 1 To Range("A1").Value
' do whatever
Next
End If
End Sub

 

[Guest]

Thanks Patrick, will try it out. Neal

Option Explicit
Sub Test_1()
MsgBox Answer_1("z", Range("G:G"))
End Sub
Function Answer_1(what As String, where As Range) As String
Dim found As Range
Set found = where.Find(what)
If found Is Nothing Then
Answer_1 = "not found"
Else
Answer_1 = found.Address
End If
End Function

Patrick Molloy
Microsoft Excel MVP
Sub Answer_2()
Dim index As Long
If IsNumeric(Range("A1")) Then
For index = 1 To Range("A1").Value
' do whatever
Next
End If
End Sub

 

[Guest]

Thanks Tim, will try it out. Neal

dim r as range

set r=thisworkbook.sheets("sheetname").range("G1")

do while r.value<>""
if r.value like "z*" then
'call your code here
end if
set r=r.offset(1,0)
loop



dim z as long, x as long

z=thisworkbook.sheets("sheetname").range("A1").value
for x=1 to z
'do your thing
next z

Hope this helps,
Tim.