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

 

[Cubist]

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 ?

Yes, you can think of dictionary as a lookup table and yes you would still need to put it in a for loop.

 

[StrawberryDreams]

Yes, you can think of dictionary as a lookup table and yes you would still need to put it in a for loop.

Thanks Cubist, Is there any reason to use scripting dictionary vs case statements - visa versa.
Both solutions look great. Is it just a matter of how one is able to read the way its written more so then the performance ?

 

[Cubist]

Thanks Cubist, Is there any reason to use scripting dictionary vs case statements - visa versa.
Both solutions look great. Is it just a matter of how one is able to read the way its written more so then the performance ?

I'm not aware if there's a difference in performance, maybe @Joe4 knows more than I do.
They each have their usage. As mentioned before, you can think of a dictionary as a lookup table, whereas select case uses logic i.e. if this then does this.
Both are applicable to your situation.

 

[StrawberryDreams]

I'm not aware if there's a difference in performance, maybe @Joe4 knows more than I do.
They each have their usage. As mentioned before, you can think of a dictionary as a lookup table, whereas select case uses logic i.e. if this then does this.
Both are applicable to your situation.

I got an active X error and the debug highlighted the Set conversions line in yellow. Maybe Ive entered something incorrectly,

Sub Store_Data_Basic()
' Takes data from one worksheet and stores in in the next empty row on another worksheet.

Dim sourceSheet As Worksheet
Dim dataSheet As Worksheet
Dim nextRow As Integer

Dim conversions As Object
 
' Make some sheet variables so we can use those instead of hard-coding sheet references in the code.
Set sourceSheet = Sheets("DataEntry")
Set dataSheet = Sheets("Data")

Set conversions = CreateObject("Scripting.Dictionary")
               
' Get the next empty row from the Data sheet.
nextRow = dataSheet.Range("A" & dataSheet.Rows.Count).End(xlUp).Offset(1).Row


 ' Define conversion rates
            conversions("Percent %") = 0.01
            conversions("grams") = 1000
            conversions("gallons") = 3.785
        
            dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("C7").Value * conversions(sourceSheet.Range("B4"))


'Another way to write the above code
'Select Case sourceSheet.Range("B4")
    'Case "Percent %"
       ' dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("C7").Value / 100
    'Case "grams"
    '    dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("C7").Value * 1000
    'Case "ppm"
    '    dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("C7").Value * 10
'End Select

End Sub

 

[Cubist]

You'd probably have to enable the library since you've never done it before. See this link.

 

[StrawberryDreams]

You'd probably have to enable the library since you've never done it before. See this link.

Unfortunately Im on a MAC and under the Tools Reference in Vba popup window there is No microsoft scripting runtime checkbox in the options to turn on.
Might have to use the CASE method which already works. Thanks for letting me know about this option.

additionally excel on MAC falls short compared to windows version.... doesn't have calculate/measure in pivot tables for example.

 

[Cubist]

No problem. You have Windows listed under your Account so I thought to mention it. Good luck.

 

[StrawberryDreams]

No problem. You have Windows listed under your Account so I thought to mention it. Good luck.

Ya that was on my old work computer which I no longer have access too Cheers

 

[StrawberryDreams]

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!

Heh Joe is this how you set up a For/Next Loop using your Case Logic ? I tried and didn't get a bug error , but it only copied one value over and it was "0" which was not the value I entered in the c7 cell which was "9" as an example.

Sub Store_Data_Basic()
' Takes data from one worksheet and stores in in the next empty row on another worksheet.

Dim sourceSheet As Worksheet
Dim dataSheet As Worksheet
Dim nextRow As Integer


 
' Make some sheet variables so we can use those instead of hard-coding sheet references in the code.
Set sourceSheet = Sheets("DataEntry")
Set dataSheet = Sheets("Data")

               
' Get the next empty row from the Data sheet.
nextRow = dataSheet.Range("A" & dataSheet.Rows.Count).End(xlUp).Offset(1).Row



'Set For next loop to copy cells with calculation into Data Sheet table

For i = 1 To 22

Select Case sourceSheet.Range("B4")
    Case "Percent %"
        dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("C7" & i).Value / 100
    Case "grams"
        dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("C7" & i).Value * 1000
    Case "ppm"
        dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("C7" & i).Value * 10

End Select

Next i


End Sub

 

[Joe4]

Note that this is not correct:

Range("C7" & i)

Think about it. This will do C71, C72, etc., and then go to C710!
You have to remove the hard-coded row number after the 7, i.e.

Range("C" & i)

Also, if you want to move to the next row every time for the other range, you will need to change this:
' Get the next empty row from the Data sheet.

nextRow = dataSheet.Range("A" & dataSheet.Rows.Count).End(xlUp).Offset(1).Row

and these references:

dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("C" & i).Value / 100

to this:

nextRow = dataSheet.Range("A" & dataSheet.Rows.Count).End(xlUp).Row

and this:

dataSheet.Cells(nextRow + i, 2).Value = sourceSheet.Range("C" & i).Value / 100