Assign a Range of values to variable to be used in same macro

[jwp]

Am trying to assign a value in a range from (A1:A100) to a variable I want to use in the same macro a 100 times.

I want a Loop to take the activecell assign it to a variable use that in a macro then move to the next row and take the next cell value.

Can someone help me pass this variable to another macro?

For Count = 0 To 100
Var = ActiveCell.Offset(Count, 0)

Call Macro 'to use the Variable Var

Next Count


End Sub

 

[CharlesChuckieCharles]

dim myRange as Range

range("A1:A100").select


For each MyRange in Selection

' your bit .....call macro with myRange.value

next myRange

' a cell is the default item of a selection

 

[FishingIsMoreFun]

You should get the idea.

Sub loopthroughrange()

Dim var As Variant
var = Range("A1:A100")
For Each i In var
MsgBox i

Next i

End Sub

 

[villy]

make your variable public so that all macro can use that variable.
E.g. Dim myVar as long
but put it outside the procedure

 

[Arul.rajesh]

Am trying to assign a value in a range from (A1:A100) to a variable I want to use in the same macro a 100 times.

I want a Loop to take the activecell assign it to a variable use that in a macro then move to the next row and take the next cell value.

Can someone help me pass this variable to another macro?

For Count = 0 To 100
Var = ActiveCell.Offset(Count, 0)

Call Macro 'to use the Variable Var

Next Count


End Sub

You could assign the Range to an array
Sub something()
MyArr=Range("A1:A100").value
End sub

The first element would be
MyArr(1,1)
second element
MyArr(2,1)
and so on..

 

[FishingIsMoreFun]

The variable var in your code is empty.

 

[FishingIsMoreFun]

Sorry , Villy is correct, I misread the post.

Public public_var As Variant
Sub firstsub()

var = Range("A1:A100")

For Each i In var
  public_var = i
  MsgBox public_var

  calledsub
  Next i
  
End Sub

Sub calledsub()
  
MsgBox "Public Variable " + CStr(public_var)

End Sub

 

[jwp]

Thank you that works nicely much appreciated.

 

[Biz]

I tried making code work and I believe few important decalartions were missed out

Dim public_var As Variant
Sub firstsub()
Dim Var As Range, i As Range

Set Var = Range("A1:A100")
For Each i In Var
  public_var = i
  MsgBox public_var
  calledsub
  Next i
  
End Sub

Sub calledsub()
  
MsgBox "Public Variable " + CStr(public_var)
End Sub

Biz

 

[FishingIsMoreFun]

Thanks Biz

It wasn't an exact answer, the user will have to tweak it a bit.
It just gives the general idea.

Additionally it might be savy to declare

Option Explicit

before the Sub Routine and the Public variable declaration (above everything else).

It forces all Variables to be declared and might be advantageous if your sub routines get large and you are passing a lot variables between two or more sub routines.

Cheers