[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/de...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...