Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 345
Default Varying a macro based on a cell's value

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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,588
Default Varying a macro based on a cell's value


"Neal Zimm" wrote in message
...
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").val ue
for x=1 to z
'do your thing
next z

Hope this helps,
Tim.


Thanks so much, Neal Z



  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,298
Default Varying a macro based on a cell's value



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


"Neal Zimm" wrote:

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

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 345
Default Varying a macro based on a cell's value

Thanks Patrick, will try it out. Neal

"Patrick Molloy" wrote:



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


"Neal Zimm" wrote:

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

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 345
Default Varying a macro based on a cell's value

Thanks Tim, will try it out. Neal


"Tim Williams" wrote:


"Neal Zimm" wrote in message
...
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").val ue
for x=1 to z
'do your thing
next z

Hope this helps,
Tim.


Thanks so much, Neal Z




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Lookup best case based on varying input???? scottgorilla[_2_] Excel Worksheet Functions 9 August 18th 08 04:51 PM
Calculate gross profit based on varying percentages Pasko1 Excel Worksheet Functions 2 May 4th 06 03:14 AM
Change value based on another cell's value mainemike Excel Discussion (Misc queries) 1 March 7th 06 07:36 PM
Varying left criteria based on 1st Letter....If Function? seve Excel Discussion (Misc queries) 2 November 25th 05 11:15 PM
Calendar based on varying information/ Pivot Table General Neil Charts and Charting in Excel 0 February 22nd 05 06:09 PM


All times are GMT +1. The time now is 01:47 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"