Excel vba cell value depending other cell

Question

Hı all,

I am trying to figure out how I can put a formule to do this. Cell a quantity Cell b unit Cell c unit price Cell d unit of cell b. So Cell a. Cell b. Cell c. Cell d. Cell e 100. gram. 200. kg. 20 If in cell b is gram chosen and cell d is. kg then cell e must be cell c isdeviced by 1000 multiplied withcell a I have the formule in Excel, but want to put it in vba. Is more secure for accidents like deleting.

This is the code I have and works well. How can I put this in the vba editor?

=(IF(B4="Gr",(A4*1),IF(B4="Ml",
   (A4*1),IF(B4="Paket",
 (A4*D4),IF(B4="Yk",
 (A4*12),IF(B4="Ck",
 (A4*8),IF(B4="Adet",(A4*1),
 IF(B4="Kg",(A4*1000),IF(B4="Lt",
 (A4*1),IF(B4>7,""))))))))*D4))


=IF(G10="Gr", ((F10*1)*E10),
IF(G10="Ml",((F10/16)*E10),
IF(G10="Paket",(F10*E10),
IF(G10="Yk",((F10/32)*E10),
IF(G10="Ck",((F10/128)*E10),
IF(G10="Adet",((F10*1)*E10),
IF(G10="Kg",((F10/1000)*E10),
IF(G10="Lt",((F10/1000)*E10),
IF(G10="","0")))))))))

Answer 1

The VBA function IIf is the function which directly corresponds to the worksheet function IF. Thus if you want a literal VBA transcription of those exact formulas it would look something like this:

Function FirstFormula() As Variant
    FirstFormula = (IIf(Range("B4").Value = "Gr", (Range("A4").Value * 1), IIf(Range("B4").Value = "Ml", _
        (Range("A4").Value * 1), IIf(Range("B4").Value = "Paket", _
        (Range("A4").Value * Range("D4").Value), IIf(Range("B4").Value = "Yk", _
        (Range("A4").Value * 12), IIf(Range("B4").Value = "Ck", _
        (Range("A4").Value * 8), IIf(Range("B4").Value = "Adet", (Range("A4").Value * 1), _
        IIf(Range("B4").Value = "Kg", (Range("A4").Value * 1000), IIf(Range("B4").Value = "Lt", _
        (Range("A4").Value * 1), IIf(Range("B4").Value > 7, "", False)))))))) * Range("D4").Value))
End Function    

Function SecondFormula() As Variant
    SecondFormula = IIf(Range("G10").Value = "Gr", ((Range("F10").Value * 1) * Range("E10").Value), _
        IIf(Range("G10").Value = "Ml", ((Range("F10").Value / 16) * Range("E10").Value), _
        IIf(Range("G10").Value = "Paket", (Range("F10").Value * Range("E10").Value), _
        IIf(Range("G10").Value = "Yk", ((Range("F10").Value / 32) * Range("E10").Value), _
        IIf(Range("G10").Value = "Ck", ((Range("F10").Value / 128) * Range("E10").Value), _
        IIf(Range("G10").Value = "Adet", ((Range("F10").Value * 1) * Range("E10").Value), _
        IIf(Range("G10").Value = "Kg", ((Range("F10").Value / 1000) * Range("E10").Value), _
        IIf(Range("G10").Value = "Lt", ((Range("F10").Value / 1000) * Range("E10").Value), _
        IIf(Range("G10").Value = "", "0", False)))))))))
End Function

Putting =FirstFormula() in a cell should be functionally equivalent to placing that exact formula in the cell, and similarly for the second formula. Note if you go this route, then the references to the formula don't automatically update when you copy the formula down, the cell references "B4", etc. are hard-wired into it.

I haven't tested the above, but it compiles nicely and it follows your logic exactly. If it doesn't suit your needs, consider using the excellent suggestion of @VBAPete.

On Edit Here is a more flexible version:

Function FirstFormula(A As Range, B As Range, D As Range) As Variant
    FirstFormula = (IIf(B.Value = "Gr", (A.Value * 1), IIf(B.Value = "Ml", _
        (A.Value * 1), IIf(B.Value = "Paket", _
        (A.Value * D.Value), IIf(B.Value = "Yk", _
        (A.Value * 12), IIf(B.Value = "Ck", _
        (A.Value * 8), IIf(B.Value = "Adet", (A.Value * 1), _
        IIf(B.Value = "Kg", (A.Value * 1000), IIf(B.Value = "Lt", _
        (A.Value * 1), IIf(B.Value > 7, "", False)))))))) * D.Value))
End Function


Function SecondFormula(E As Range, F As Range, G As Range) As Variant
    SecondFormula = IIf(G.Value = "Gr", ((F.Value * 1) * E.Value), _
        IIf(G.Value = "Ml", ((F.Value / 16) * E.Value), _
        IIf(G.Value = "Paket", (F.Value * E.Value), _
        IIf(G.Value = "Yk", ((F.Value / 32) * E.Value), _
        IIf(G.Value = "Ck", ((F.Value / 128) * E.Value), _
        IIf(G.Value = "Adet", ((F.Value * 1) * E.Value), _
        IIf(G.Value = "Kg", ((F.Value / 1000) * E.Value), _
        IIf(G.Value = "Lt", ((F.Value / 1000) * E.Value), _
        IIf(G.Value = "", "0", False)))))))))
End Function

Then in a worksheet, for example, you could use

=FirstFormula(A4,B4,D4)

and it will be equivalent. Furthermore, the references to A4, B4, D4 would update as expected when the formula is copied down a range.

On Further Edit If you want to pursue the idea of using VBA to insert the formulas directly in a cell, you would use something like:

Dim form As String
form = "=IF(G10=""Gr"", ((F10*1)*E10),"
form = form & "IF(G10=""Ml"",((F10/16)*E10),"
form = form & "IF(G10=""Paket"",(F10*E10),"
form = form & "IF(G10=""Yk"",((F10/32)*E10),"
form = form & "IF(G10=""Ck"",((F10/128)*E10),"
form = form & "IF(G10=""Adet"",((F10*1)*E10),"
form = form & "IF(G10=""Kg"",((F10/1000)*E10),"
form = form & "IF(G10=""Lt"",((F10/1000)*E10),"
form = form & "IF(G10="""",""0"")))))))))"

Range("H10").Formula = form

In the above I built up the formula string in stages in the interest of readability. You don't need to do that, but the result would be, well, less readable.

Not that if you want quote marks to appear inside a string you have to double them up (use "" rather than ") so that VBA can tell that the overall string isn't supposed to end there.