Hi Excel Gurus,
I've run into a problem where I lose (or else don't capture) values correctly when I run a subroutine after calling for the next worksheet.
The routine works fine if I don't try to automate the selection of the next sheet to run this routine. Each sheet I try the routine on independently, it works as intended.
Due to my limited VBA skills I repeat a function multiple times to capture a dynamic variable (column number) by searching for the text inside the upper most cell of each used column. I have 4 versions of this routine. From ColStep to ColStep4. After all four have run I have my variables MyCol, MyCol2, MyCol3, and MyCol4.
Dim mySheetCount As Long
For mySheetCount = 4 To Sheets.Count
Sheets(mySheetCount).Select
For ColStep = 1 To ColCount Step 1
If headerText_1 = "Heading_1" Then
ActiveCell.Select
MyCol = ActiveCell.Column
Exit For
Else
ActiveCell.Offset(0, 1).Select
headerText_1 = ActiveCell.Value
End If
Next ColStep
'maybe redundant to go to this range
Range("A1").Select
For ColStep2 = 1 To ColCount Step 1
If headerText_2 = "Heading_2" Then
ActiveCell.Select
MyCol2 = ActiveCell.Column
n = Cells(Rows.Count, MyCol2).End(xlUp).Row
Exit For
Else
ActiveCell.Offset(0, 1).Select
headerText_2 = ActiveCell.Value
End If
Next ColStep2
I run into the problem at this point and it is mind baffling to me. 'j' has been set as an Integer. 'n' is the number of used Rows.
For j = 2 To n Step 1
a = Cells(j, MyCol).Value
b = Cells(j, MyCol2).Value
c = Cells(j, MyCol3).Value
d = Cells(j, MyCol4).Value
If
... code does its work...
End If
Next j
Next mySheetCount
Here's the problem, once the focus shifts to the next sheet in the workbook MyCol's value is captured correctly but the other column values (MyCol2, MyCol3, and MyCol4) lose their values and seem to default to '1' and the remaining action is limited to column A (first column). It appears that my 'For Next' loop isn't working starting at ColStep2 once I've switched sheets.
However, the variable 'n' captures the correct value on the next sheet in the For ColStep2 function.
Totally stumped!
If anyone thinks they can help I'll more than happy to show the whole code. If I comment out the For/Next for mySheetCount the codes works perfectly.
Thanks in advance,
Matt
I've run into a problem where I lose (or else don't capture) values correctly when I run a subroutine after calling for the next worksheet.
The routine works fine if I don't try to automate the selection of the next sheet to run this routine. Each sheet I try the routine on independently, it works as intended.
Due to my limited VBA skills I repeat a function multiple times to capture a dynamic variable (column number) by searching for the text inside the upper most cell of each used column. I have 4 versions of this routine. From ColStep to ColStep4. After all four have run I have my variables MyCol, MyCol2, MyCol3, and MyCol4.
Dim mySheetCount As Long
For mySheetCount = 4 To Sheets.Count
Sheets(mySheetCount).Select
For ColStep = 1 To ColCount Step 1
If headerText_1 = "Heading_1" Then
ActiveCell.Select
MyCol = ActiveCell.Column
Exit For
Else
ActiveCell.Offset(0, 1).Select
headerText_1 = ActiveCell.Value
End If
Next ColStep
'maybe redundant to go to this range
Range("A1").Select
For ColStep2 = 1 To ColCount Step 1
If headerText_2 = "Heading_2" Then
ActiveCell.Select
MyCol2 = ActiveCell.Column
n = Cells(Rows.Count, MyCol2).End(xlUp).Row
Exit For
Else
ActiveCell.Offset(0, 1).Select
headerText_2 = ActiveCell.Value
End If
Next ColStep2
I run into the problem at this point and it is mind baffling to me. 'j' has been set as an Integer. 'n' is the number of used Rows.
For j = 2 To n Step 1
a = Cells(j, MyCol).Value
b = Cells(j, MyCol2).Value
c = Cells(j, MyCol3).Value
d = Cells(j, MyCol4).Value
If
... code does its work...
End If
Next j
Next mySheetCount
Here's the problem, once the focus shifts to the next sheet in the workbook MyCol's value is captured correctly but the other column values (MyCol2, MyCol3, and MyCol4) lose their values and seem to default to '1' and the remaining action is limited to column A (first column). It appears that my 'For Next' loop isn't working starting at ColStep2 once I've switched sheets.
However, the variable 'n' captures the correct value on the next sheet in the For ColStep2 function.
Totally stumped!
If anyone thinks they can help I'll more than happy to show the whole code. If I comment out the For/Next for mySheetCount the codes works perfectly.
Thanks in advance,
Matt