Results 1 to 6 of 6

Thread: [RESOLVED]Excel - Simple add and subtract of variables

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Feb 2006
    Posts
    96

    Resolved [RESOLVED]Excel - Simple add and subtract of variables

    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!)

    VB Code:
    1. Sub bforr()
    2. Dim i As Double
    3. Dim j As Double
    4. Dim k As Double
    5. Dim gSheet As Worksheet
    6. Dim tSheet As Worksheet
    7. Dim eSheet As Worksheet
    8.  
    9.     Set gSheet = ActiveWorkbook.Worksheets("Grund")
    10.     Set tSheet = ActiveWorkbook.Worksheets("Tilrettet")
    11.     Set eSheet = ActiveWorkbook.Worksheets("Ekskl.ny-øgede og tlf")
    12.    
    13. For i = 1 To 100
    14.     If gSheet.Cells(i, 1).Value = "BFORR" Then
    15.         gSheet.Activate
    16.         gSheet.Rows(i).EntireRow.Copy
    17.         tSheet.Activate
    18.         tSheet.Rows(i).PasteSpecial Paste:=xlValues
    19.        ElseIf gSheet.Cells(i, 1).Value = "Z3684 BANKAKT BG BANK" Then
    20.         gSheet.Activate
    21.         gSheet.Rows(i).EntireRow.Copy
    22.         tSheet.Activate
    23.         tSheet.Rows(i).PasteSpecial Paste:=xlValues
    24.             For j = 1 To 100
    25.             If gSheet.Cells(j, 1).Value = "N3394 CENTRAL INKASSO BG" Then
    26.                 gSheet.Activate
    27.                 gSheet.Range(Cells(j, 3), Cells(j, 3)).End(xlToRight).Copy
    28.                 tSheet.Activate
    29.                 tSheet.Range(Cells(i, 3), Cells(i, 3)).End(xlToRight).PasteSpecial Paste:=xlValues, operation:=xlAdd
    30.             End If
    31.             Next j
    32.     ElseIf gSheet.Cells(i, 1).Value = "Z3798 BANKAKT DANSKE BANK" Then
    33.         gSheet.Activate
    34.         gSheet.Rows(i).EntireRow.Copy
    35.         tSheet.Activate
    36.         tSheet.Rows(i).PasteSpecial Paste:=xlValues
    37.             For k = 1 To 100
    38.             If gSheet.Cells(k, 1).Value = "W3900 RESS. OG STABSOMRÅDE" Then
    39.                 gSheet.Activate
    40.                 gSheet.Range(Cells(k, 3), Cells(k, 3)).End(xlToRight).Copy
    41.                 tSheet.Activate
    42.                 tSheet.Range(Cells(i, 3), Cells(i, 3)).End(xlToRight).PasteSpecial Paste:=xlValues, operation:=xlAdd
    43.             End If
    44.             If gSheet.Cells(k, 1).Value = "Z3988 BANKAKT STAB" Then
    45.                 gSheet.Activate
    46.                 gSheet.Range(Cells(k, 3), Cells(k, 3)).End(xlToRight).Copy
    47.                 tSheet.Activate
    48.                 tSheet.Range(Cells(i, 3), Cells(i, 3)).End(xlToRight).PasteSpecial Paste:=xlValues, operation:=xlAdd
    49.             End If
    50.             If gSheet.Cells(k, 1).Value = "N3394 CENTRAL INKASSO BG" Then
    51.                 gSheet.Activate
    52.                 gSheet.Range(Cells(k, 3), Cells(k, 3)).End(xlToRight).Copy
    53.                 tSheet.Activate
    54.                 tSheet.Range(Cells(i, 3), Cells(i, 3)).End(xlToRight).PasteSpecial Paste:=xlValues, operation:=xlSubtract
    55.             End If
    56.             Next k
    57.     ElseIf gSheet.Cells(i, 1).Value = "K4456 BANKAKT IRLAND" Then
    58.         gSheet.Activate
    59.         gSheet.Rows(i).EntireRow.Copy
    60.         tSheet.Activate
    61.         tSheet.Rows(i).PasteSpecial Paste:=xlValues
    62.     ElseIf gSheet.Cells(i, 1).Value = "K4477 BANKAKT NORDIRLAND" Then
    63.         gSheet.Activate
    64.         gSheet.Rows(i).EntireRow.Copy
    65.         tSheet.Activate
    66.         tSheet.Rows(i).PasteSpecial Paste:=xlValues
    67.         End If
    68. Next i
    69.  
    70. Application.CutCopyMode = False
    71.  
    72.     Set gSheet = Nothing
    73.     Set tSheet = Nothing
    74.     Set eSheet = Nothing
    75.  
    76. End Sub

    thanx
    Nick
    Last edited by direktoren; May 3rd, 2006 at 01:22 AM. Reason: RESOLVED

  2. #2
    Frenzied Member DKenny's Avatar
    Join Date
    Sep 2005
    Location
    on the good ship oblivion..
    Posts
    1,171

    Re: Excel - Simple add and subtract of variables

    but it fails to add and subtract some ranges
    Where in this code are you doing any addition or subtraction??
    Last edited by DKenny; Apr 28th, 2006 at 08:17 AM.
    Declan

    Don't forget to mark your Thread as resolved.
    Take a moment to rate posts that you think are helpful

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Feb 2006
    Posts
    96

    Re: Excel - Simple add and subtract of variables

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

  4. #4
    Fanatic Member VBAhack's Avatar
    Join Date
    Dec 2004
    Location
    Sector 000
    Posts
    610

    Re: Excel - Simple add and subtract of variables

    Quote Originally Posted by direktoren
    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.
    VB Code:
    1. tSheet.Rows(i).PasteSpecial Operation:=xlAdd
    Suggest you turn on the macro recorder and do some operations. You'll learn a lot.

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Feb 2006
    Posts
    96

    Re: Excel - Simple add and subtract of variables

    Quote Originally Posted by direktoren
    VB Code:
    1. tSheet.Range(Cells(i, 3), Cells(i, 3)).End(xlToRight).PasteSpecial Paste:=xlValues, operation:=xlSubtract

    Nick
    I would mean i'm already doing this!!

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Feb 2006
    Posts
    96

    Re: Excel - Simple add and subtract of variables

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width