VBA Call Function with If Then Statement

M

Mo796

New Member
Joined
Mar 12, 2022
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Hello All, I am using a macro that tracks data onto another worksheet as well as calling another function to place an indicator of +/- and color the cell green/ red respectively depending on whether the total net worth calculated increases or decreases. These macros work well on their own, and I have gotten it to run the first part of my if/ then statement, but it does not run the other half of the if/ then. So it always places a - and red. When run alone the if/then works fine so it appears to be an issue with my call function. Am I missing an argument for the function?


Sub Track()

Sheets("Balance Timelines").Select

Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrBelow = 0

Dim sourceWs As Worksheet, dstWs As Worksheet


With Range("B3")

Set sourceWs = Sheets("Balance Entry Sheet")
Set dstWs = Sheets("Balance Timelines")

sourceWs.Range("B3").Copy

Call dstWs.Range("A4").End(xlUp).Offset(1).PasteSpecial(Paste:=xlPasteValues)

End With

With Range("B4")

Set sourceWs = Sheets("Balance Entry Sheet")
Set dstWs = Sheets("Balance Timelines")

sourceWs.Range("B4").Copy

Call dstWs.Range("B4").End(xlUp).Offset(1).PasteSpecial(Paste:=xlPasteValues)

End With

With Range("B5")

Set sourceWs = Sheets("Balance Entry Sheet")
Set dstWs = Sheets("Balance Timelines")

sourceWs.Range("B5").Copy

Call dstWs.Range("C4").End(xlUp).Offset(1).PasteSpecial(Paste:=xlPasteValues)

End With

With Range("B6")

Set sourceWs = Sheets("Balance Entry Sheet")
Set dstWs = Sheets("Balance Timelines")

sourceWs.Range("B6").Copy

Call dstWs.Range("D4").End(xlUp).Offset(1).PasteSpecial(Paste:=xlPasteValues)

End With

With Range("D25")

Set sourceWs = Sheets("Debt")
Set dstWs = Sheets("Balance Timelines")

sourceWs.Range("D25").Copy

Call dstWs.Range("E4").End(xlUp).Offset(1).PasteSpecial(Paste:=xlPasteValues)

End With

With Range("G4")

Set dstWs = Sheets("Balance Timelines")

dstWs.Range("G4").End(xlUp).Offset(1).Value = Now()

End With

Call FormatCells
Call TCalculation

End Sub

Sub FormatCells()

Sheets("Balance Timelines").Select

If Range("F3").Value - Range("F4").Value > 0 Then
Range("H3").Value = "+"
Range("H3").Interior.Color = 5287936
Else
Range("H3").Value = "-"
Range("H3").Interior.Color = 255
End If

End Sub

Sub TCalculation()

Sheets("Balance Timelines").Select

Range("F3") = WorksheetFunction.Sum(Range("A3:D3")) - Range("E3")

End Sub

- - - - - - - - - - - - - - - - - -
The issue I am having is that it is not running this half of my first call, it always formats the cell as - and red:
If Range("F3").Value - Range("F4").Value > 0 Then
Range("H3").Value = "+"
Range("H3").Interior.Color = 5287936
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
That code makes not too much sense to me. First, dstWs is a sheet object so you can't call it like a procedure (which is what the call statement is for) - unless maybe you have a function elsewhere by that name, in which case it's not something I'd ever do. Your With block isn't needed at all because IF you have multiple methods to invoke or properties to set or reference with an object, you use a With block. The format is like

VBA Code: 
  With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Select Folder"
    .AllowMultiSelect = False
    If .Show <> -1 Then Exit Sub
    myPath = .SelectedItems(1) & "\"
  End With
Everything that comes after a dot applies to/with the file dialog. There are only 4 of them there, but sometimes there's many more. Aside from that, my suggestion would be to put a break point at the start of your code step through. Watch the flow and the values of variables and other things (those lines have to be executed before the values will be set).

Please use code tags (vba button on posting toolbar) when posting code to maintain indentation and improve readability. Would be great if you reposted it that way.
 
Upvote 0
Hi,
Instead of using the Select method, better it would be better to make the sheet object explicit. If it still doesn't work as intended, better check the values in ranges F3 and F4 in the worksheet named Balance Timelines.

VBA Code: 
Sub FormatCells()
    With Sheets("Balance Timelines")
        If .Range("F3").Value - .Range("F4").Value > 0 Then
            .Range("H3").Value = "+"
            .Range("H3").Interior.Color = 5287936
        Else
            .Range("H3").Value = "-"
            .Range("H3").Interior.Color = 255
        End If
    End With
End Sub

Also, this is not directly related to the question, it seems "With" statements and "Call" statements written in the procedure named Track don't do anything, and no need to set the same worksheet variables again and again. So those can be removed as follows. And TCalculation can be rewritten without using the Select method as well.

VBA Code: 
Sub Track()
    Dim sourceWs As Worksheet, dstWs As Worksheet

    Set sourceWs = Sheets("Balance Entry Sheet")
    Set dstWs = Sheets("Balance Timelines")

    sourceWs.Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrBelow = 0

    sourceWs.Range("B3").Copy
    dstWs.Range("A4").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

    sourceWs.Range("B4").Copy
    dstWs.Range("B4").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

    sourceWs.Range("B5").Copy
    dstWs.Range("C4").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

    sourceWs.Range("B6").Copy
    dstWs.Range("D4").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

    Set sourceWs = Sheets("Debt")
    sourceWs.Range("D25").Copy
    dstWs.Range("E4").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

    dstWs.Range("G4").End(xlUp).Offset(1).Value = Now()

    Call FormatCells
    Call TCalculation
End Sub  

Sub TCalculation()
    With Sheets("Balance Timelines")
        .Range("F3") = WorksheetFunction.Sum(.Range("A3:D3")) - .Range("E3")
    End With
End Sub
 
Upvote 0
Micron said:
That code makes not too much sense to me. First, dstWs is a sheet object so you can't call it like a procedure (which is what the call statement is for) - unless maybe you have a function elsewhere by that name, in which case it's not something I'd ever do. Your With block isn't needed at all because IF you have multiple methods to invoke or properties to set or reference with an object, you use a With block. The format is like

VBA Code: 
  With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Select Folder"
    .AllowMultiSelect = False
    If .Show <> -1 Then Exit Sub
    myPath = .SelectedItems(1) & "\"
  End With
Everything that comes after a dot applies to/with the file dialog. There are only 4 of them there, but sometimes there's many more. Aside from that, my suggestion would be to put a break point at the start of your code step through. Watch the flow and the values of variables and other things (those lines have to be executed before the values will be set).

Please use code tags (vba button on posting toolbar) when posting code to maintain indentation and improve readability. Would be great if you reposted it that way.
Click to expand...
Thank you for the advise, but it does not quite answer my question. The with and dstWs portions is not an issue and works as intended. The issue I am having is the functionality of calling the IF/ THEN statement. I am trying to call it into my original SUB. The SUBs work on their own and the IF/ Then works half way when called, but it only populates the last half resulting in "-" and color 255, it does not properly attribute the first IF value and color when called. Im going to try to use code tags so Im sorry if I post the format wrong.

VBA Code: 
Sub FormatCells()

Sheets("Balance Timelines").Select

    If Range("F3").Value - Range("F4").Value > 0 Then
    Range("H3").Value = "+"
    Range("H3").Interior.Color = 5287936
    Else
    Range("H3").Value = "-"
    Range("H3").Interior.Color = 255
    End If
   
End Sub
 
Upvote 0
Colo said:
Hi,
Instead of using the Select method, better it would be better to make the sheet object explicit. If it still doesn't work as intended, better check the values in ranges F3 and F4 in the worksheet named Balance Timelines.

VBA Code: 
Sub FormatCells()
    With Sheets("Balance Timelines")
        If .Range("F3").Value - .Range("F4").Value > 0 Then
            .Range("H3").Value = "+"
            .Range("H3").Interior.Color = 5287936
        Else
            .Range("H3").Value = "-"
            .Range("H3").Interior.Color = 255
        End If
    End With
End Sub

Also, this is not directly related to the question, it seems "With" statements and "Call" statements written in the procedure named Track don't do anything, and no need to set the same worksheet variables again and again. So those can be removed as follows. And TCalculation can be rewritten without using the Select method as well.

VBA Code: 
Sub Track()
    Dim sourceWs As Worksheet, dstWs As Worksheet

    Set sourceWs = Sheets("Balance Entry Sheet")
    Set dstWs = Sheets("Balance Timelines")

    sourceWs.Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrBelow = 0

    sourceWs.Range("B3").Copy
    dstWs.Range("A4").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

    sourceWs.Range("B4").Copy
    dstWs.Range("B4").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

    sourceWs.Range("B5").Copy
    dstWs.Range("C4").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

    sourceWs.Range("B6").Copy
    dstWs.Range("D4").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

    Set sourceWs = Sheets("Debt")
    sourceWs.Range("D25").Copy
    dstWs.Range("E4").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

    dstWs.Range("G4").End(xlUp).Offset(1).Value = Now()

    Call FormatCells
    Call TCalculation
End Sub 

Sub TCalculation()
    With Sheets("Balance Timelines")
        .Range("F3") = WorksheetFunction.Sum(.Range("A3:D3")) - .Range("E3")
    End With
End Sub
Click to expand...
Thank you for helping me clean up the macro. However, when I run it, the first half of the IF statement is still not working. It is only giving me a result of "-" and color 255 instead of the first half as it should. All other cells are correct in the sheet and the rest of the macro is working as intended. This is the portion that is not correctly working:
VBA Code: 
Sub FormatCells()
    With Sheets("Balance Timelines")
        If .Range("F3").Value - .Range("F4").Value > 0 Then
            .Range("H3").Value = "+"
            .Range("H3").Interior.Color = 5287936
        Else
            .Range("H3").Value = "-"
            .Range("H3").Interior.Color = 255
        End If
    End With
End Sub
 
Upvote 0
For visual reference of how the IF/ Then code is working when used on its own vs called.
 

Attachments

  • Capture.PNG
    Capture.PNG
    12.5 KB · Views: 4
Upvote 0
All right, so you can set a Breakpoint on the first half of the IF statement so that you can track what's happening in the code. (Instead of setting a Breakpoint, you can add a Stop statement in your function as follows. Then debug your code by pressing the F8 key. It executes your code one by one line so you can know how the cord works there. And when the code line ".Range("F3").Value - .Range("F4").Value" is highlighted, you can paste the following code in the immediate window then press Enter. So you can know the result of the calculation.

VBA Code: 
Debug.Print .Range("F3").Value - .Range("F4").Value

スクリーンショット 2022-08-31 095724.jpg
 
Upvote 0
By the way, what's in the cells F3 and F4? Are those formulas? If so, the calculation has been done when the procedure is called?

Now the bottleneck is that it doesn't work only when it's called from another procedure, so I believe that we better check the differences of the values in those cells between it's called and runs alone in both cases.
 
Upvote 0
Colo said:
By the way, what's in the cells F3 and F4? Are those formulas? If so, the calculation has been done when the procedure is called?

Now the bottleneck is that it doesn't work only when it's called from another procedure, so I believe that we better check the differences of the values in those cells between it's called and runs alone in both cases.
Click to expand...
F4 is not a formula, F3 is what the macro affects. The row 3 columns A-H are updated in the first part of my macro. The first SUB that I am trying to attach this IF/ Then to copies and pastes data from other tabs into a new row on the 3rd row. So F3 is updated every time the macro is run. The IF/Then is intended to compare the values of F3 to F4 to determine and display if there was an increase ( "+" and "5287936") or a decrease ("-" and "255") in value. Even before its called it will have data that should populate a + and the color green, but when called it always formats the cell H3 to - and red.
 
Upvote 0
I got it. So please inform us how the code was working by debugging. That's the point.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
VBA Conditional Formatting
Replies
2
Views
658
Mo796
M
M
  • Solved
How do I copy and paste data to another worksheets row while keeping and shifting old data downwards
Replies
4
Views
569
Mo796
M
J
  • Question
Change specific values (not all) in a VBA sub
2
Replies
16
Views
458
juscuz419
J
Keebler
  • Solved
adding sort to a complicated array of rabbit holes and ...
2
Replies
12
Views
317
Keebler
Keebler
I
  • Question
Modify VBA script to count rows with data in multiple sheets
Replies
0
Views
98
irishjsk
I

Forum statistics

Threads
1,215,063
Messages
6,122,934
Members
449,094
Latest member
teemeren

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top