[RESOLVED]Tricky Excel?

[direktoren]

Hi

A new problem has arisen in my macro (surprise!)...

What I have is;

Case "Credit"
gSheet.Rows(j).EntireRow.Copy tSheet.Rows(j)
For l = 5 To 350
Select Case gSheet.Cells(l, 1).Value
Case "W3900"
For m = l To gSheet.Cells(l, 2).End(xlDown).Row
Select Case gSheet.Cells(m, 2).Value
Case gSheet.Cells(j, 2).Value
gSheet.Activate
gSheet.Range(Cells(m, 3), Cells(m, 3).End(xlToRight)).Copy
tSheet.Activate
tSheet.Range(Cells(j, 3), Cells(j, 3).End(xlToRight)).PasteSpecial Paste:=xlValues, operation:=xlAdd
Application.CutCopyMode = False
End Select
Next m
Case "Z3988"
For m = l To gSheet.Cells(l, 2).End(xlDown).Row
Select Case gSheet.Cells(m, 2).Value
Case gSheet.Cells(j, 2).Value
gSheet.Activate
gSheet.Range(Cells(m, 3), Cells(m, 3).End(xlToRight)).Copy
tSheet.Activate
tSheet.Range(Cells(j, 3), Cells(j, 3).End(xlToRight)).PasteSpecial Paste:=xlValues, operation:=xlAdd
Application.CutCopyMode = False
End Select
Next m
Case "N3394"
For m = l To gSheet.Cells(l, 2).End(xlDown).Row
Select Case gSheet.Cells(m, 2).Value
Case gSheet.Cells(j, 2).Value
gSheet.Activate
gSheet.Range(Cells(m, 3), Cells(m, 3).End(xlToRight)).Copy
tSheet.Activate
tSheet.Range(Cells(j, 3), Cells(j, 3).End(xlToRight)).PasteSpecial Paste:=xlValues, operation:=xlSubtract
Application.CutCopyMode = False
End Select
Next m
Case "3472Hen"
For m = l To gSheet.Cells(l, 2).End(xlDown).Row
Select Case gSheet.Cells(m, 2).Value
Case gSheet.Cells(j, 2).Value
gSheet.Activate
gSheet.Range(Cells(m, 3), Cells(m, 3).End(xlToRight)).Copy
tSheet.Activate
tSheet.Range(Cells(j, 3), Cells(j, 3).End(xlToRight)).PasteSpecial Paste:=xlValues, operation:=xlSubtract
Application.CutCopyMode = False
End Select
Next m
End Select
Next l

This worked fine for me for a while, until I discovered a flaw! The nested cases can only perform their operations if the initial case is positive - this is however not always the case.

Can I in any way bypass this, so the nested cells will perform their operations in the same destination as they would have if the initial case had a match?

regards nick

[RobDog888]

You have two select statements. One outter and one inner. The outter match causes the inner select to be evaluated. If there is a match in the coded nested cases then it will fire.

I think your loop inside the initial select may be bad logic as if you want to the outter case to be evaluated again then the loop needs to go outsite of the outter or mail select.

[direktoren]

Well, what I have is actually;


For i = 5 To 350
Select Case gSheet.Cells(i, 1).Value
Case "Z3798"
For j = i To gSheet.Cells(i, 2).End(xlDown).Row
Select Case gSheet.Cells(j, 2).Value
Case "Credit"
gSheet.Rows(j).EntireRow.Copy tSheet.Rows(j)
For l = 5 To 350
Select Case gSheet.Cells(l, 1).Value
Case "W3900"
For m = l To gSheet.Cells(l, 2).End(xlDown).Row
Select Case gSheet.Cells(m, 2).Value
Case gSheet.Cells(j, 2).Value
gSheet.Activate
gSheet.Range(Cells(m, 3), Cells(m, 3).End(xlToRight)).Copy
tSheet.Activate
tSheet.Range(Cells(j, 3), Cells(j, 3).End(xlToRight)).PasteSpecial Paste:=xlValues, operation:=xlAdd
Application.CutCopyMode = False
End Select
Next m
End Select
Next l


So I have got 2 outer cases - and yes, I do realize that nothing will fire unless I have a match on my outer cases.
But what I'm wondering is whether there is an easier way to solve my problem, than applying fixed values to all destination ranges instead of variable ones (This code is repeated for lots and lots of reg no. so I would like an alternative!). With fixed values my problem would be somewhat solved, however it will take LOTS of time to implement it!

[RobDog888]

It may be easier to use a Cells.Find method passing varing criteria in a loop.

There is a detailed explaination in the help file on its arguments.
expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaxl11/html/xlmthFind1_HV05201251.asp

[direktoren]

Okay - So i went long...

I decided to skip the variable row names and apply fixed row names in stead (still bearing in mind that a minor change in organization will force me to apply new row numbers once again).

Heres what I've got;
For i = 5 To 350
Select Case gSheet.Cells(i, 1).Value
Case "Z3798 BANKAKT DANSKE BANK"
For j = i To gSheet.Cells(i, 2).End(xlDown).Row
Select Case gSheet.Cells(j, 2).Value
Case "Kundegrupper"
gSheet.Rows(j).EntireRow.Copy tSheet.Rows(82)
Case "Finansielle kunder"
gSheet.Rows(j).EntireRow.Copy tSheet.Rows(83)
Case "Kreditinstitutter"
gSheet.Rows(j).EntireRow.Copy tSheet.Rows(84)
'... and so on

' I then use pastespecial to add other values into these rows;

Case "W3900 RESS. OG STABSOMRÅDE"
For j = i To gSheet.Cells(i, 2).End(xlDown).Row
Select Case gSheet.Cells(j, 2).Value
Case "Kundegrupper"
gSheet.Activate
gSheet.Rows(j).EntireRow.Copy
tSheet.Activate
tSheet.Rows(82).PasteSpecial Paste:=xlValues, operation:=xlAdd
Application.CutCopyMode = False
Case "Finansielle kunder"
gSheet.Activate
gSheet.Rows(j).EntireRow.Copy
tSheet.Activate
tSheet.Rows(83).PasteSpecial Paste:=xlValues, operation:=xlAdd
Application.CutCopyMode = False
Case "Kreditinstitutter"
gSheet.Activate
gSheet.Rows(j).EntireRow.Copy
tSheet.Activate
tSheet.Rows(84).PasteSpecial Paste:=xlValues, operation:=xlAdd
Application.CutCopyMode = False


My problem is, and I'm starting to find the typical for Excel VBA, that it doesn't add all of the rows - some of them it will add, while others it won't!

What's happening?

[direktoren]

Solved it - forgot for a moment that i was using Case instead for an If statement, so it didn't loop through the columns - fix it now though...