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?

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

[vba]

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)))"
[/vba]

[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.

If I can only have a guide to start the VBA code for this file.

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 ...

[vba]
Range("BG8").Formula =
[/vba]

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


[vba]
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
[/vba]

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

[genracela]

sorry, I have another question...

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

[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