VBA - Return macro result to next blank column

[windb]

Hi there,

I have several macros which have been written individually which run simple formulas and return the data to the active column. Eg. Macro 1 enters a simple formula in cell X2, to return the result, whereby column X is the first blank column at the end of my dataset.

To avoid running these macros individually I’m planning to create a ‘master’ macro to “call” each macro so that they run sequentially. Eg. Call macro 1
Call macro 2 etc

However, with each passing macro I need to ensure the results return to the next blank column only, I.e macro 2 doesn’t overwrite the results of macro 1. So, I’d like the results in column X, Y, Z etc

Can you anyone advise I achieve this please?

I can locate general formulas to find the next blank row or column but can’t figure how this fits with the call macro VBA code. Note- feel free to advise if call macro is not the best approach here.

Also, my dataset consistently has the same fields/ headers which should hopefully help.

Many thanks

 

[Snakehips]

@windb Maybe along the lines of below.

'include parameters for your individual Macros to recieve row and columns, when called.
Sub Mac1(r, lc)
Cells(r, lc) = 999
End Sub

Sub Mac2(r, lc)
Cells(r, lc) = "ddd"
End Sub

Sub Mac3(r, lc)
Cells(r, lc) = "ddd12nre"
End Sub

Sub Multi_Macs()
'establish current row of interest and assign to r
r = 3    ' <  hard set to row 3 for convenience of example
'establish last used column in row r
'OR set initial lc to 24 if first blank column is always to be x ?
lc = Cells(r, Columns.Count).End(xlToLeft).Column
'update the r and or lc as required for each call
Call Mac1(r, lc + 1)
Call Mac2(r, lc + 1)
Call Mac3(r, lc + 1)

End Sub

Hope that helps.

 

[windb]

@windb Maybe along the lines of below.

'include parameters for your individual Macros to recieve row and columns, when called.
Sub Mac1(r, lc)
Cells(r, lc) = 999
End Sub

Sub Mac2(r, lc)
Cells(r, lc) = "ddd"
End Sub

Sub Mac3(r, lc)
Cells(r, lc) = "ddd12nre"
End Sub

Sub Multi_Macs()
'establish current row of interest and assign to r
r = 3    ' <  hard set to row 3 for convenience of example
'establish last used column in row r
'OR set initial lc to 24 if first blank column is always to be x ?
lc = Cells(r, Columns.Count).End(xlToLeft).Column
'update the r and or lc as required for each call
Call Mac1(r, lc + 1)
Call Mac2(r, lc + 1)
Call Mac3(r, lc + 1)

End Sub

Hope that helps.

That’s great, thank you @Snakehips. Will give this a try tomorrow and let you know!

 

[Snakehips]

@windb Just notice that I posted a misleading edit of the Multi_Macs code.

Hopefully below is correct and more illustrative/

Sub Multi_Macs()
'establish current row of interest and assign to r
r = 3    ' <  hard set to row 3 for convenience of example
'establish last used column in row r
'OR set initial lc to 24 if first blank column is always to be x ?
lc = Cells(r, Columns.Count).End(xlToLeft).Column
'update the r and or lc as required for each call

Call Mac1(r, lc + 1)
Call Mac2(r, lc + 2)
Call Mac3(r, lc + 3)


'OR
lc = Cells(r, Columns.Count).End(xlToLeft).Column
lc = lc + 1
Call Mac1(r, lc)

lc = lc + Round(17 / 12)
Call Mac2(r, lc)

lc = lc + 1
Call Mac3(r, lc)

End Sub

 

[windb]

@windb Just notice that I posted a misleading edit of the Multi_Macs code.

Hopefully below is correct and more illustrative/

Sub Multi_Macs()
'establish current row of interest and assign to r
r = 3    ' <  hard set to row 3 for convenience of example
'establish last used column in row r
'OR set initial lc to 24 if first blank column is always to be x ?
lc = Cells(r, Columns.Count).End(xlToLeft).Column
'update the r and or lc as required for each call

Call Mac1(r, lc + 1)
Call Mac2(r, lc + 2)
Call Mac3(r, lc + 3)


'OR
lc = Cells(r, Columns.Count).End(xlToLeft).Column
lc = lc + 1
Call Mac1(r, lc)

lc = lc + Round(17 / 12)
Call Mac2(r, lc)

lc = lc + 1
Call Mac3(r, lc)

End Sub

Thank you, works great for what I need! Greatly appreciate the help @Snakehips. Appreciate the clarification too.