IF Function in VBA
I managed to create a macro to show the following:
If (I3<>0,I3*G3,H3*G3) and this repeats itself for cell N3,R3, V3,Z3 ETC.
Option Explicit
Sub Eg()
Range("J3, N3,R3, V3,Z3,AD3,AH3,AL3,AP3,AT3,Ax3,BB3,XF3,BJ3").Formula = "=IF(RC[-1]<>0,RC[-1]*RC[-3],RC[-2]*RC[-3])"
End Sub
Let me explain a bit more how this should work:
This report needs to be downloaded from an application.
The macro needs to be attached to this report so that when I download the report the macro automatically runs this formula in the appropriate columns.
Also I 'll have to populate the spreadhseet for all the rows with this formula.
The columns where the formula should sit are not blank but this needs to be catered for in the report automatically once the macro is run.
What am I missing here?
Hope you'll be able to help.
Thanks.
| Attachment | Size |
|---|---|
| Example_Actual_Spend_on_Time_Sheet_16_11_v1.xls | 40.5 KB |
Because the generator of the
Because the generator of the spreadsheet creates a wrong or rather a bad formated sheet; The result is that the spreadsheet is set as text and any formula you will type will display the text of the formula but will not compute anything.
To run the calculation, you'll have to convert each column that contain the formula for example, select the column J, then click on Data, Text to column and press three times the the key Enter... You might see the calculation for this unique column.
So to resolve your ask, you have to create a process that doing these manipulations instead of you.
IF Function in VBA
If I press the key enter, it will erase the data i already have there.
Is this what you meant?
No that will not erase data
No that will not erase data but only convert the content as an Excel content property such as a date, a number or in your case, a formula...
IF Function in VBA
Thanks Argy.
A quick question... do you need to put the formula in
=IF(I3<>0,I3*G3,H3*G3) for the macro to work or just the code?
I'm getting really confused now. This is my first attempt at macros!
IF Function in VBA
Thanks Argy.
A quick question... do you need to put the formula in
=IF(I3<>0,I3*G3,H3*G3) for the macro to work or just the code?
I'm getting really confused now. This is my first attempt at macros!
Well, the best way is to
Well, the best way is to define automatically the process. But I rather suggest you to write a generic subroutine...
Here is a example of subroutine (copy and paste this bloc into your module):
Sub Eg()
'---------------------------------------------------------------------------
' Procedure : Eg
' DateTime : 16/11/2012
' Author : Argyronet
' Purpose : Apply a formula to a set of cells
'...........................................................................
' Parameters : none
' Return Codes : none
'...........................................................................
' Notice : Press F5 to run the macro
'---------------------------------------------------------------------------
'Constants
Const CELL_COLOR As Long = 15849925
Const FIRST_ROW As Integer = 1
Const TARGET_ROW As Integer = 3
Dim oRng As Range
Dim intColumnIndex As Integer
Dim strRangeAddress As String
Dim blnApplyConversion As Boolean
On Error GoTo L_ErrEg
'Ask for conversion
blnApplyConversion = (MsgBox("Do you want to apply conversion for each target column ?", vbQuestion + vbYesNo, "Text to column") = vbYes)
'For the 14 target cells (4 by 4)
For C = 10 To 62 Step 4
'Build the address A1
strRangeAddress = Cells(TARGET_ROW, C).Address(False, False)
'Set the range object
Set oRng = Range(strRangeAddress)
'Define the range formula and its features
With oRng
.FormulaR1C1 = "=IF(RC[-1]<>0,RC[-1]*RC[-3],RC[-2]*RC[-3])"
.Interior.Color = CELL_COLOR
.HorizontalAlignment = xlCenter
.Font.Bold = True
End With
'Select the current colum
Columns(C).Select
'Build the address of the first row of the current column
strRangeAddress = Replace(Cells(TARGET_ROW, C).Address, TARGET_ROW, FIRST_ROW)
'Apply conversion
If blnApplyConversion Then
Selection.TextToColumns Destination:=Range(strRangeAddress), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
End If
Next
On Error GoTo 0
L_ExEg:
'Hide formulas...
ActiveWindow.DisplayFormulas = False
'Freeing objects
Set oRng = Nothing
Exit Sub
L_ErrEg:
MsgBox Err.Description, 48, Err.Source
Resume L_ExEg
End Sub
If function in VBA
Thanks a lot Argy.
I have copied this generic sub routine.
This will tell me whetehr there's an error every time I run the report right?
So can I still use the same code as before:
Option Explicit
Sub Eg()
Range("J3,N3,R3,V3,Z3,AD3,AH3,AL3,AP3,AT3,AX3,BB3,BF3,BJ3").Formula = "=IF(RC[-1]<>0,RC[-1]*RC[-3],RC[-2]*RC[-3])"
End Sub
No, it not reports you about
No, it not reports you about any error except if the subroutine raise an error...
The displayed message is only designed to confirm if you want or not, applying the column conversion.
You can use your intial code, but the subroutine I wrote you can execute all what you ask for.
If Function in VBA
Thanks Argyronet.
I'll try it and let you know. I still need to ahve my command button or just press F5 to run the macro?
If Function in VBA
IT works! but only for the first row...it needs to do it for the other rows as well.