PDA

View Full Version : IF function in VBA - Can't COPY



KAT1234
01-10-2018, 06:17 AM
I'm trying to do an if function to calculate a margin depending on value in field G2. It is not working at all.

Pls help = my code is below


Private Sub CommandButton8_Click()
Dim score As Integer, result As String
score = Range("G2").Value
If score > 50001 Then result = "=SUM(ABC!D2)+(ABC!D2*2.2%)"
Range("K2").Value = result
End Sub

SamT
01-10-2018, 07:51 AM
Dim Score As double 'or as Long, but not as Integer. Integer limit is 32K
Dim Result as double

score = Range("G2")

If score > 50001 Then result = Sheets("ABC").Range("D2")*1.022 'More concise than addition and multiplication
Range("K2") = result
'Or Range("K2") = CStr(result). But you can't perform math on a Textual number.
'Even If 1 < "2" will fail


If you want a formula in K2 then use
Dim Result as String
Range("K2").Formula = result
Where Result = "=SUM(ABC!D2)+(ABC!D2*2.2%)"
Or, where Result = "=ABC!D2*1.022"

JKwan
01-10-2018, 07:53 AM
I am guessing that you have not fully qualify your sheets, so when you press your button, things don't work correctly

Private Sub CommandButton8_Click()
Dim WS As Worksheet

Set WS = Worksheets("Sheet2")

With WS
If .Range("G2") > 50001 Then
.Range("K2") = "=SUM(ABC!D2)+(ABC!D2*2.2%)"
End If
End With
End Sub

KAT1234
01-10-2018, 10:00 AM
I am guessing that you have not fully qualify your sheets, so when you press your button, things don't work correctly

Private Sub CommandButton8_Click()
Dim WS As Worksheet

Set WS = Worksheets("Sheet2")

With WS
If .Range("G2") > 50001 Then
.Range("K2") = "=SUM(ABC!D2)+(ABC!D2*2.2%)"
End If
End With
End Sub



This does not work. It just comes up with 0 value in K2

KAT1234
01-10-2018, 10:02 AM
Dim Score As double 'or as Long, but not as Integer. Integer limit is 32K
Dim Result as double

score = Range("G2")

If score > 50001 Then result = Sheets("ABC").Range("D2")*1.022 'More concise than addition and multiplication
Range("K2") = result
'Or Range("K2") = CStr(result). But you can't perform math on a Textual number.
'Even If 1 < "2" will fail


If you want a formula in K2 then use
Dim Result as String
Range("K2").Formula = result
Where Result = "=SUM(ABC!D2)+(ABC!D2*2.2%)"
Or, where Result = "=ABC!D2*1.022"


I am trying to use a formula in the field and it does not seem to work even if I use Dim Result as String? What am I doing wrong?

SamT
01-10-2018, 11:44 AM
What worksheet(s) are ranges G2 and K2 on?

Place Option Explicit at the top of the Code page.
Use VBA Menu Debug >> Compile to find some errors
Place the cursor in the sub and press F8 to step thru the code and find more errors

JKwan
01-10-2018, 05:55 PM
I am sure this can be solved easily if you attach your workbook.