Stumped by a For Next problem

[Matt_O]

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

 

[Matt_O]

I found a work around. I created a separate module for the For/Next for calling the next sheet in the workbook. I'll likely never know what was causing the original issue.

 

[ukmikeb]

Hi

A couple of observations :-
1, Always use variables dimmed as Long when refering to Rows and Columns.

2, If "Heading_1" and "Heading_2" always exist (once in any sheet) wouldn't it be easier to capture the Column number using MATCH?

hth

 

[Matt_O]

Hi Mike,

Thanks for the reply.

I thought choosing Integer versus Long was based on how large the number might get. The row count would likely never go past 50. But if using Long is the best practice then I'll go and update my code.

As for MATCH. Well, I've never used that function!

Time to do some research. I'm guessing using MATCH will clean up my sprawling code.

Thanks,
Matt

 

[ukmikeb]

Hi

The MATCH would be something like (untested) :-

MyCol = Application.Match("Heading_1", Range("A1:Z1"),0)

which would replace the first loop and of course avoid selecting cells.

hth

 

[Matt_O]

Thanks again Mike.

I did some research on using Match and this is exactly what I should be using. I figured out the syntax for a variable loop up target and dynamic look up array:

MyCol = Application.Match(str, Range(Cells(1, 1), Cells(1, ColCount)), 0)

I did get stumped for a while when first testing because my test cells were not contiguous. That mystified me for all!

Time to update my code!

Cheers,
Matt