Macro that uses Cells in Column A being automatically reused for columns B,C,D...

[VBAIntermediate]

Hi All,

I have a macro that uses cell references from Column B, it uses a lot of cell references in Column B. Is it possible for me to set it up a macro or adjust it, so I could re-use that code for columns B, C, D ect keep the row references. Below is a hugely simplified version of what I coded. Problem is there are like 50 rows and I just need to have the macros repeat but using the column references from column C and D and E ect - I hope that makes sense


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]NAME[/TD]
[TD="align: center"]John[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]AGE[/TD]
[TD="align: center"]32[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]HEIGHT[/TD]
[TD="align: center"]6ft[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]WEIGHT[/TD]
[TD="align: center"]140[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

What I have, for example, is (ignore the blatant syntax issues)

Sub AgemultipliedByMadeupNumbers ()

Range("B2").Value =x
x*54654654 = y

End Sub

Is there a way for me to reuse that code but for column C and D, without copying and pasting it all, because my code is really long.

 

[PatOBrien198]

In it's simplest form..... use an argument in your main subroutine.

Sub CallMainMacro()

AgemultipliedByMadeupNumbers ("B")
AgemultipliedByMadeupNumbers ("C")
AgemultipliedByMadeupNumbers ("D")

end sub


Sub AgemultipliedByMadeupNumbers(strColumnLetter)

 Range(strColumnLetter & "2").Value =x
 x*54654654 = y

 End Sub

 

[VBAIntermediate]

In it's simplest form..... use an argument in your main subroutine.

Sub CallMainMacro()

AgemultipliedByMadeupNumbers ("B")
AgemultipliedByMadeupNumbers ("C")
AgemultipliedByMadeupNumbers ("D")

end sub


Sub AgemultipliedByMadeupNumbers(strColumnLetter)

 Range(strColumnLetter & "2").Value =x
 x*54654654 = y

 End Sub

Thank you for this!

Is it possible for me to, when calling the subroutine, have the Column value be determined by what cell the cursor is in?

 

[PatOBrien198]

Sure.... something like:

Call AgemultipliedByMadeupNumbers(Left(activecell.address,2))

There are other ways as well.....