In my macro I have a subroutine which (using a for loop) goes through the rows of a table and writes a comment in column V depending on what is in column S. During this loop it also counts the number of times it says "New" in column S. Now I want to pass this value up in to the main macro and down in to another subroutine. How do I do this or is my approach here wrong?
If I understand correctly, its usually not possible to return values from subs in excel VBA, but you can from a function. However I don't think a function would be suitable here (I might be misunderstanding how functions work in VBA and/or not realising their full potential!).
So how do I smuggle out the variable/value from the sub and in to another one? Is the only option
Here's a really rough example of my code:
Sub MainMacro () Call CommentSub Call NumberOfRowsToCopy End sub Sub CommentSub Dim Counter As Integer For Counter = 1 to 500 If Cells(Counter, "S") = "New" Then NewOrderLineCounter = NewOrderLineCounter + 1 Cells(Counter, "V").Select ActiveCell.FormulaR1C1 = "New Line" End If Next Counter End sub Sub NumberOfRowsToCopy ActiveSheet.Range("$A$12:$T$1001").AutoFilter Field:=16, Criteria1:= _ "New" ActiveSheet.Range("B15:N" & NewOrderLineCounter).SpecialCells(xlCellTypeVisible).Select End sub
(Btw, I'm aware that there maybe "better" ways to get the number of rows that need to be copied here (thereby eliminating the need to pass values between subs) but I think I've tried all of them and none worked. I think its the format of the excel sheet but that's another problem and you gotta work with what you're given, right?)