[RESOLVED]Excel - Simple add and subtract of variables

[direktoren]

Hi again guys

I am doing a new automasation process at work. but it fails to add and subtract some ranges - I don't get any error messages, but no calculations either!!

Here is my code - (and yes I know it isn't pretty, but my VB XP isn't that high and I'm in a hurry!)

Sub bforr()
Dim i As Double
Dim j As Double
Dim k As Double
Dim gSheet As Worksheet
Dim tSheet As Worksheet
Dim eSheet As Worksheet
 
    Set gSheet = ActiveWorkbook.Worksheets("Grund")
    Set tSheet = ActiveWorkbook.Worksheets("Tilrettet")
    Set eSheet = ActiveWorkbook.Worksheets("Ekskl.ny-øgede og tlf")
    
For i = 1 To 100
    If gSheet.Cells(i, 1).Value = "BFORR" Then
        gSheet.Activate
        gSheet.Rows(i).EntireRow.Copy
        tSheet.Activate
        tSheet.Rows(i).PasteSpecial Paste:=xlValues
       ElseIf gSheet.Cells(i, 1).Value = "Z3684 BANKAKT BG BANK" Then
        gSheet.Activate
        gSheet.Rows(i).EntireRow.Copy
        tSheet.Activate
        tSheet.Rows(i).PasteSpecial Paste:=xlValues
            For j = 1 To 100
            If gSheet.Cells(j, 1).Value = "N3394 CENTRAL INKASSO BG" Then
                gSheet.Activate
                gSheet.Range(Cells(j, 3), Cells(j, 3)).End(xlToRight).Copy
                tSheet.Activate
                tSheet.Range(Cells(i, 3), Cells(i, 3)).End(xlToRight).PasteSpecial Paste:=xlValues, operation:=xlAdd
            End If
            Next j
    ElseIf gSheet.Cells(i, 1).Value = "Z3798 BANKAKT DANSKE BANK" Then
        gSheet.Activate
        gSheet.Rows(i).EntireRow.Copy
        tSheet.Activate
        tSheet.Rows(i).PasteSpecial Paste:=xlValues
            For k = 1 To 100
            If gSheet.Cells(k, 1).Value = "W3900 RESS. OG STABSOMRÅDE" Then
                gSheet.Activate
                gSheet.Range(Cells(k, 3), Cells(k, 3)).End(xlToRight).Copy
                tSheet.Activate
                tSheet.Range(Cells(i, 3), Cells(i, 3)).End(xlToRight).PasteSpecial Paste:=xlValues, operation:=xlAdd
            End If
            If gSheet.Cells(k, 1).Value = "Z3988 BANKAKT STAB" Then
                gSheet.Activate
                gSheet.Range(Cells(k, 3), Cells(k, 3)).End(xlToRight).Copy
                tSheet.Activate
                tSheet.Range(Cells(i, 3), Cells(i, 3)).End(xlToRight).PasteSpecial Paste:=xlValues, operation:=xlAdd
            End If
            If gSheet.Cells(k, 1).Value = "N3394 CENTRAL INKASSO BG" Then
                gSheet.Activate
                gSheet.Range(Cells(k, 3), Cells(k, 3)).End(xlToRight).Copy
                tSheet.Activate
                tSheet.Range(Cells(i, 3), Cells(i, 3)).End(xlToRight).PasteSpecial Paste:=xlValues, operation:=xlSubtract
            End If
            Next k
    ElseIf gSheet.Cells(i, 1).Value = "K4456 BANKAKT IRLAND" Then
        gSheet.Activate
        gSheet.Rows(i).EntireRow.Copy
        tSheet.Activate
        tSheet.Rows(i).PasteSpecial Paste:=xlValues
    ElseIf gSheet.Cells(i, 1).Value = "K4477 BANKAKT NORDIRLAND" Then
        gSheet.Activate
        gSheet.Rows(i).EntireRow.Copy
        tSheet.Activate
        tSheet.Rows(i).PasteSpecial Paste:=xlValues
        End If
Next i
 
Application.CutCopyMode = False
 
    Set gSheet = Nothing
    Set tSheet = Nothing
    Set eSheet = Nothing
 
End Sub

thanx
Nick

[DKenny]

but it fails to add and subtract some ranges

Where in this code are you doing any addition or subtraction??

[direktoren]

Well i'm using the pastespecial operation add and subtract, since it (usually) does the job perfectly

[VBAhack]

Well i'm using the pastespecial operation add and subtract, since it (usually) does the job perfectly

Then you need to specify the operation in the paste special command.

tSheet.Rows(i).PasteSpecial Operation:=xlAdd

Suggest you turn on the macro recorder and do some operations. You'll learn a lot.

[direktoren]

tSheet.Range(Cells(i, 3), Cells(i, 3)).End(xlToRight).PasteSpecial Paste:=xlValues, operation:=xlSubtract

Nick

I would mean i'm already doing this!!

[direktoren]

Solved it - in my hurry to get the whole thing done in time, I misplaced a ) - it should of course be located after the end statement, so it is included in the range...

Nick