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

 

[StrawberryDreams]

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

cool thanks, I got a bug in the " dataSheet.Cells(nextRow + i, 2).Value = sourceSheet.Range("C" & i).Value / 100 "

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).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 + i, 2).Value = sourceSheet.Range("C" & i).Value / 100
    Case "grams"
        dataSheet.Cells(nextRow + i, 2).Value = sourceSheet.Range("C" & i).Value * 1000
    Case "ppm"
        dataSheet.Cells(nextRow + i, 2).Value = sourceSheet.Range("C" & i).Value * 10

End Select

Next i


End Sub

 

[StrawberryDreams]

--

 

[StrawberryDreams]

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

Kinda worked.
Copied, but didn't place them into the table horizontally.

Sorry had to repost - didn't copy the full mini window for XLBB

Basic Nutrient Calculator3.xlsm
	A	B	C	D
1	Data Entry
2	Unit choice	%
3
4	Product	Value
5	apple	3
6	pear	4
7	grape	2
8
DataEntry

Cells with Data Validation
Cell	Allow	Criteria
B2:B3	List	%, grams, kg

Basic Nutrient Calculator3.xlsm
	A	B	C	D
1	Data Table
2	Unit	Apple	Pear	Grape
3
4
5
6
7
8		0.03
9		0.04
10		0.02
Data2

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("Data2")

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




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

For i = 5 To 7

Select Case sourceSheet.Range("B2")
    Case "%"
        dataSheet.Cells(nextRow + i, 2).Value = sourceSheet.Range("B" & i).Value / 100
    Case "grams"
        dataSheet.Cells(nextRow + i, 2).Value = sourceSheet.Range("B" & i).Value * 1
    Case "kg"
        dataSheet.Cells(nextRow + i, 2).Value = sourceSheet.Range("B" & i).Value * 1000

End Select

Next i


End Sub

 

[Joe4]

OK, let's take a big step back here. This "simple" VBA question has turned into quite a bit more (as they often do).
I don't think your code is set up to do anything remotely like what it looks like you want to do.

Can you please post what you have to start with, and then show us exactly what you want to end up with (and explain the logic behind it)?
And please do NOT oversimplify it (lest you may get an oversimplified answer that won't work on your real data structure).
Rather than trying to fix up faulty code, I prefer to start from scratch, when I have a clear vision of what exactly it is you are trying to accomplish.

 

[StrawberryDreams]

OK, let's take a big step back here. This "simple" VBA question has turned into quite a bit more (as they often do).
I don't think your code is set up to do anything remotely like what it looks like you want to do.

Can you please post what you have to start with, and then show us exactly what you want to end up with (and explain the logic behind it)?
And please do NOT oversimplify it (lest you may get an oversimplified answer that won't work on your real data structure).
Rather than trying to fix up faulty code, I prefer to start from scratch, when I have a clear vision of what exactly it is you are trying to accomplish.

Sorry I thought it might have been straightforward - but shows how inexperienced I am with VBA and excel.
Originally my code worked when I wanted to simply copy cells from one worksheet and place them into a second worksheet table.

The issues as you've pointed out are when I wanted to add a calculation and place them the same way. Ive placed an XL2bb screenshot showing what I would like with examples. My original code which works but doesn't have the calculation added to it. Also for some weird reason if you have cleared the data table it will not properly place data into the table unless the first row has been filled.

Basic data calculator test.xlsm
	A	B	C	D	E	F	G
1	Data Entry Worksheet
2	Unit choice	kg
3					pressing the button will copy
4	Product	Value			Data entry form over to
5	apple	3			the data table , then clear
6	pear	2			the data entry cells
7	grape	5
8
9
10	Data Table Worksheet
11	unit	Apple	Pear	Grape
12	first row can	not be blank	before new	data
13	grams	2	4	9
14	%	1	9	12
15	kg	3	2	5
16
17	What I want to be placed in the Data table is the following :
18	The unit will convert all relevant data to grams
19	unit	Apple	Pear	Grape
20	first row can	not be blank	before new	data		vba calculation
21	grams	2	4	9		if = "grams" * 1
22	%	0.01	0.09	0.12		if = "%" / 100
23	kg	3000	2000	5000		if = "kg" * 1000
24
DataEntry

Cells with Data Validation
Cell	Allow	Criteria
B2:B3	List	%, grams, kg

Sub Store_Data()

' 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("DataTable")

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


'Input Unit from DataEntry to DataTable Sheet

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

'Input Values from DataEntry to DataTable Sheet

dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("B5").Value
dataSheet.Cells(nextRow, 3).Value = sourceSheet.Range("B6").Value
dataSheet.Cells(nextRow, 4).Value = sourceSheet.Range("B7").Value



End Sub


'Following Code is from code Joe originally provided.
'Set For next loop to copy cells with calculation into Data Sheet table

'For i = 5 To 7

'Select Case sourceSheet.Range("B2")
    'Case "%"
        'dataSheet.Cells(nextRow + i, 2).Value = sourceSheet.Range("B" & i).Value / 100
    'Case "grams"
       ' dataSheet.Cells(nextRow + i, 2).Value = sourceSheet.Range("B" & i).Value * 1
    'Case "kg"
        'dataSheet.Cells(nextRow + i, 2).Value = sourceSheet.Range("B" & i).Value * 1000

'End Select

'Next i


'End Sub

 

[StrawberryDreams]

OK, let's take a big step back here. This "simple" VBA question has turned into quite a bit more (as they often do).
I don't think your code is set up to do anything remotely like what it looks like you want to do.

Can you please post what you have to start with, and then show us exactly what you want to end up with (and explain the logic behind it)?
And please do NOT oversimplify it (lest you may get an oversimplified answer that won't work on your real data structure).
Rather than trying to fix up faulty code, I prefer to start from scratch, when I have a clear vision of what exactly it is you are trying to accomplish.

Ok I figured it out!

Your code works great it's just needed one line to be an i-3 instead of i+2. I kept the offset code which keeps the values placed horizontally instead of vertically which was happening when the offset was removed. But then it placed the code 7 columns to the left of the table but at least all in a single row as wanted. So I took the value of i and had to minus an amount to get it back into the table properly.

Sub Store_Data()

' 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("DataTable")

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

For i = 5 To 7

Select Case sourceSheet.Range("B2")
    Case "%"
        dataSheet.Cells(nextRow, i - 3).Value = sourceSheet.Range("B" & i).Value / 100
    Case "grams"
        dataSheet.Cells(nextRow, i - 3).Value = sourceSheet.Range("B" & i).Value * 1
    Case "kg"
        dataSheet.Cells(nextRow, i - 3).Value = sourceSheet.Range("B" & i).Value * 1000

End Select

Next i




End Sub••••ˇˇˇˇ

thanks again Joe for all your help and learning !

 

[Joe4]

You are welcome.
Glad you were able to figure it out overnight when I was off-line!

 

[Cristian Buse]

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.

FastDictionary is great for Mac and in fact it's faster than Scripting.Dictionary on Windows. Basically a fully drop-in replacement but cross-platform.