simple vba syntax question

[StrawberryDreams]

Still learning vba...

I have a simple Data entry User form (sourceSheet) , that is currently taking the cell B2 and copying it into another worksheet table (dataSheet)
The following code works:

dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("B2").Value



I would like to change what it does so the value it is copying B2 is divided by 100. How do
I write the VBA to do that ---- the following does not work :

dataSheet.Cells(nextRow, 2).Formula = "=sourceSheet.Range(B2).Value / 100"

My goal is to take the value the user enter in the data entry sheet and turn it into percent form in the data table.
Cheers

 

[Joe4]

You don't want ".Formula", you want ".Value", i.e.

dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range(B2).Value/100

 

[StrawberryDreams]

You don't want ".Formula", you want ".Value", i.e.

dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range(B2).Value/100

Thanks Joe, it was a simple fix thanks.

One more question, if I were to have an additional cell with data validation where the user could choose a unit type like ( %, grams, Gallons etc)
I could have the above vba code with an If Else , statement to either /100 or * 1000, or * 3.785 etc. depending on the user selection , what would that via code look like ?

Cheers

 

[Joe4]

Yes, that should be able to be done.

 

[StrawberryDreams]

Yes, that should be able to be done.

something like this perhaps ?

A2 is the cell with the dropdown validation choices

'Set unitChoice = sourceSheet.Range("A2").Value
'If
'unitchoice = "%"
'dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("C7").Value / 100

'Else
'unitchoice = "grams"
'dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("C7").Value * 1000

'Else
'unitchoice = "gallons"
'dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("C7").Value * 3.785

'End If

 

[Joe4]

I would use a "CASE" statement instead - much cleaner!
See: MS Excel: How to use the CASE Statement (VBA)

So it would look something like this:

Select Case sourceSheet.Range("A2")
    Case "%"
        dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("C7").Value / 100
    Case "grams"
        dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("C7").Value * 1000
    Case "gallons"
        dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("C7").Value * 3.785
End Select

Also note, please use Code Tags when posting VBA code (like I did), which makes it MUCH easier to read
See: How to Post Your VBA Code

 

[StrawberryDreams]

I would use a "CASE" statement instead - much cleaner!
See: MS Excel: How to use the CASE Statement (VBA)

So it would look something like this:

Select Case sourceSheet.Range("A2")
    Case "%"
        dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("C7").Value / 100
    Case "grams"
        dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("C7").Value * 1000
    Case "gallons"
        dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("C7").Value * 3.785
End Select

Also note, please use Code Tags when posting VBA code (like I did), which makes it MUCH easier to read
See: How to Post Your VBA Code

Oh cool Ive never used case, thanks for the link on Case too - will read up on what that does....
If My Data Entry User Form happens to have 20 rows of entries, would I just write 20 lines of vba code for each case ?
I'm fine with that since its just copy and paste, but would there be a more elegant way to write the code if there
is that many cells with multiple choice options / formulas ? Otherwise can be work for a future code change later.
thanks.

 

[Joe4]

Oh cool Ive never used case, thanks for the link on Case too - will read up on what that does....
If My Data Entry User Form happens to have 20 rows of entries, would I just write 20 lines of vba code for each case ?
I'm fine with that since its just copy and paste, but would there be a more elegant way to write the code if there
is that many cells with multiple choice options / formulas ? Otherwise can be work for a future code change later.
thanks.

If you are doing the same thing on all 20 lines, just use a loop (probably a "For...Next" loop).
No need to repeat your VBA code!

 

[Cubist]

Another option is to use dictionary.

    Dim conversions As Object
    Set conversions = CreateObject("Scripting.Dictionary")

    ' Define conversion rates
    conversions("%") = 0.01
    conversions("grams") = 1000
    conversions("gallons") = 3.785

    dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("C7").Value *conversions(sourceSheet.Range("A2"))

 

[StrawberryDreams]

Another option is to use dictionary.

    Dim conversions As Object
    Set conversions = CreateObject("Scripting.Dictionary")

    ' Define conversion rates
    conversions("%") = 0.01
    conversions("grams") = 1000
    conversions("gallons") = 3.785

    dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("C7").Value *conversions(sourceSheet.Range("A2"))

I've created a conversion chart using index/match in my earlier days until I found out Excel has an actual built in convert function. Albeit some conversions are not in the built in convert list so I've still had to resort to building a conversion table to look up from. I'm guessing this vba code is kinda like building a conversion look up table.

You would still need to wrap your code in a For / next loop as Joe mentioned to apply the conversions to all the rows / before they get pasted into the data table , correct ?