Solved: Where can I find a VBA guide?

[genracela]

I need to create a VBA to calculate 13 columns with 13 different formulas.

My problem is I don't know how to modify my formulas to VBA.

Where can I find at least a guide to help me turn my cell formulas to VBA codes?:help

Example:
AG8=IF(VLOOKUP(A11,Category!A$1:B$65536,2,0)=1,1,IF(L11=0,1.4,IF(L11<=L$6,1,1.4)))

Thanks!

[Bob Phillips]

It is as simple as



Range("AG8").Formula = "=IF(VLOOKUP(A11,Category!A$1:B$65536,2,0)=1,1,IF(L11=0,1.4,IF(L11<=L$6,1 ,1.4)))"

[genracela]

What if I want to calculate column AG8:G1000?


range(AG8:G1000)?

[Bob Phillips]

Do you mean AG1000?

What would the formula be in AG9?

[genracela]

yes, i mean AG1000.

AG8 to AG1000 has the same formula.

[genracela]

I tried 1 of my formula, but it doesn't work:(

[genracela]

Actually, the attached template is the one I'm working on right now.:dunno

If I can only have a guide to start the VBA code for this file.
:help
Thanks in advance!

[Paul_Hossler]

Couple of things

1. Put the VBA for the Sub "calc" in a standard module (VBE, Insert, Module). You had it in worksheet code space

2. Good idea to use Option Explicit and always define your variables

3. The humongous formula line ...


Range("BG8").Formula =


was red because there was a syntax error -- when you use strings within a string and you need the 'inner' string to have it's own quotes (like for your WS formula) you need to double them


4. I broke the formula line into pieces and built it since it was so long



Option Explicit
Sub calc()
Dim sFormula As String
sFormula = "=IF(OR(AND(CX8=""T"",CY8=""T"",CZ8=""T"",DA8=""T""),"
sFormula = sFormula & "AND(CX8=""T"",CY8=""T"",CZ8=""F"",DA8=""T""),"

sFormula = sFormula & "AND(CX8=""T"",CY8=""T"",CZ8=""T"",DA8=""F""),"
sFormula = sFormula & "AND(CX8=""T"",CY8=""T"",CZ8=""F"",DA8=""F""),"
sFormula = sFormula & "AND(CX8=""T"",CY8=""F"",CZ8=""T"",DA8=""T""),"
sFormula = sFormula & "AND(CX8=""F"",CY8=""T"",CZ8=""T"",DA8=""T"")),""G"","
sFormula = sFormula & "(IF(OR(AND(CX8=""T"",CY8=""F"",CZ8=""F"",DA8=""T""),"
sFormula = sFormula & "AND(CX8=""F"",CY8=""T"",CZ8=""F"",DA8=""T""),"
sFormula = sFormula & "AND(CX8=""T"",CY8=""F"",CZ8=""T"",DA8=""F""),"
sFormula = sFormula & "AND(CX8=""F"",CY8=""F"",CZ8=""T"",DA8=""T""),"
sFormula = sFormula & "AND(CX8=""F"",CY8=""T"",CZ8=""T"",DA8=""F""),"
sFormula = sFormula & "AND(CX8=""F"",CY8=""F"",CZ8=""T"",DA8=""F"")),""Y"","
sFormula = sFormula & "(IF(OR(AND(CX8=""F"",CY8=""F"",CZ8=""F"",DA8=""F""),"
sFormula = sFormula & "AND(CX8=""F"",CY8=""F"",CZ8=""F"",DA8=""T""),"
sFormula = sFormula & "AND(CX8=""T"",CY8=""F"",CZ8=""F"",DA8=""F""),"
sFormula = sFormula & "AND(CX8=""F"",CY8=""T"",CZ8=""F"",DA8=""F"")),""R"")))))"
Range("BG8").Formula = sFormula

End Sub


Paul

[genracela]

Thanks for the reply!:)

follow up question.

This is only for Col/row BG8, what if I also want the formula to calculate in BG9 to BG1000?

will I do


range("BG8:BG1000").Formula =


instead of

range("BG8")

[genracela]

hey, i got it!

It automatically calculate when I did the BG8:BG1000!

Thanks a million!!!! Mwahhhhhh!!!:clap: :bow: :beerchug:

[genracela]

sorry, I have another question...

what if I want to calculate BG8 until the last row? not exactly BG1000. :help

[jolivanes]

You could try this if Column DD is a relative column for your calculations.
If not, change DD to a column you want to use and has a bearing on the calculations. Make sure to change the column offset (here it is -49) also.
Try it on a backup file first please.



Range("DD8", Range("DD" & Rows.Count).End(xlUp)).Offset(, -49).Formula = sFormula


Hope it works.

John