How to run macro on all sheets

[ijalab22]

I have a macro, which I want to run on all the sheets of my workbook. The code is pasted below:


Also I have a problem, I need the macro to display to me the value of the row,column value I am selecting, but its not doing the same. Please find the macro and advise...I need to do this program asap...

Sub FindAddressColumn()


   Dim rngAddress                  As Range
   Set rngAddress = Range("A1:A10").Find("south")
   Range(rngAddress, rngAddress.End(xlToRight)).Select
   myRow = ActiveCell.row
   MsgBox myRow


   Dim rngAddress0                 As Range
   Set rngAddress0 = Range("A1:Z1").Find("rt3")
   Range(rngAddress0, rngAddress0.End(xlDown)).Select
   col1 = Split(ActiveCell(1).Address(1, 0), "$")(0)
   MsgBox col1

   Dim cellval                     As String
   cellval = GetValue(row:=myRow, col:=col1)
   MsgBox (cellval)

End Sub


Function GetValue(row As Integer, col As String)
   GetValue = ActiveSheet.Cells(row, col)
End Function

 

[patel45]

try this code

Sub FindAddressColumn()
Dim rngAddress As Range, [COLOR=#b22222]myrow As Integer, col1 As Integer[/COLOR]
Set rngAddress = Range("A1:A10").Find("south")
Range(rngAddress, rngAddress.End(xlToRight)).Select
myrow = ActiveCell.row
MsgBox myrow
Dim rngAddress0 As Range
Set rngAddress0 = Range("A1:Z1").Find("rt3")
Range(rngAddress0, rngAddress0.End(xlDown)).Select
col1 = [COLOR=#b22222]Val(Split(ActiveCell(1).Address(1, 0), "$")(0))[/COLOR]
MsgBox col1
Dim cellval As String
cellval = GetValue(myrow, col1)
MsgBox (cellval)
End Sub

Function GetValue(row As Integer, col As [COLOR=#b22222]Integer[/COLOR])
GetValue = ActiveSheet.Cells(row, col)
End Function

if problems paste a link to a sample file for testing

 

[ijalab22]

Mr.patel thanks for the reply, but ur macro doesnt seem to work.

find the errors and the entire program. Thanks in advance for ur help.

----------------------------------------------------------------------------------------------

east	rt1	rt2	rt3	rt4	rt5
north	bdifu	bcdjk	dvf	vfsd	dsv
west	dsv	d	dvf	sdf	v
south	cfeds	c	wdw	sdf	vdfs
news	dsv	sdv	sfdvwe	e	h

<tbody>
</tbody>

Sub FindAddressColumn()
Dim rngAddress As Range, myrow As Integer, col1 As Integer
Set rngAddress = Range("A1:A10").Find("south")
Range(rngAddress, rngAddress.End(xlToRight)).Select
myrow = ActiveCell.row
MsgBox myrow
Dim rngAddress0 As Range
Set rngAddress0 = Range("A1:Z1").Find("rt3")
Range(rngAddress0, rngAddress0.End(xlDown)).Select
col1 = Val(Split(ActiveCell(1).Address(1, 0), "$"))  
MsgBox col1
Dim cellval As String
cellval = GetValue(myrow, col1)
MsgBox (cellval)
End Sub
 
Function GetValue(row As Integer, col As Integer)
GetValue = ActiveSheet.Cells(row, col)
End Function

Run time error 13 Type mismatch
In the highlighted line because column address is D where as we defined it as integer … wat will I do ….

 

[Rick Rothstein]

try this code

Sub FindAddressColumn()
Dim rngAddress As Range, [COLOR=#b22222]myrow As Integer, col1 As Integer[/COLOR]
Set rngAddress = Range("A1:A10").Find("south")
Range(rngAddress, rngAddress.End(xlToRight)).Select
myrow = ActiveCell.row
MsgBox myrow
Dim rngAddress0 As Range
Set rngAddress0 = Range("A1:Z1").Find("rt3")
Range(rngAddress0, rngAddress0.End(xlDown)).Select
col1 = [COLOR=#b22222]Val(Split(ActiveCell(1).Address(1, 0), "$")(0))[/COLOR]
MsgBox col1
Dim cellval As String
cellval = GetValue(myrow, col1)
MsgBox (cellval)
End Sub

Function GetValue(row As Integer, col As [COLOR=#b22222]Integer[/COLOR])
GetValue = ActiveSheet.Cells(row, col)
End Function

if problems paste a link to a sample file for testing

You should declare those variables as Long, not Integer. There are two reasons for this... first, there is no savings using Integer on modern 32/64 bit computers (Integer values will be placed in memory in the memory-space of a Long any way)... second, specifically for the "myrow" variable, there are more rows available on a worksheet than an Integer can hold.

 

[ijalab22]

Thanks Rick, but I tried ur suggestion of declaring variables as Long, but same error persists...Could you have a look in to it

 

[patel45]

change this line

col1 = Val(Split(ActiveCell(1).Address(1, 0), "$")(0))

to

col1 = Val(Split(ActiveCell(1).Address(1, 0), "$")([COLOR=#ff0000]1[/COLOR]))

i did not understand the goal of this code