Enter Value in One Worksheet and Generate Many Values in Another Sheet 



Home  More Excel (VBA) Training Videos 

This Excel training video is
based on the following query: Hi, First of all, thank you for all the interesting excel VBA tutorials you've made! I'm a swedish student from Malmö, studying bachelor in civil engineering and is now writing my candidate paper. In my paper I thought to include an excelfile but I've got some problems with macros and VBA. What I want to do: The user gives a number (days) and excel (the macro) generates a list with the same amount of numbers as it is days (a list with 1,2,3...) in another sheet. the user will also write a price per unit that is supposed to complete the list next to the numbers. I don't know if my explanation above is any good, so I'll give you my code. I't doesn't work and I would be very glad if you could help me. CODE: Private Sub cmd_addit_Click() Dim number, x, x1, antaltimmar As Integer x1 = 1 'row 1 (so excel knows) 'I've made a list starting in L1 with 1 L2 with 2 and so on. x = 4 'row 4, this is the starting row, where I want excel to start paste info. number = 1 'it is always at least 1 day. Do Until number = antaldagar 'Amount of days should be the same as in the list. Worksheets("Blad1").Select 'chose sheet1 to get info from antaldagar = Range("C10") 'antaldagar value from C10. Worksheets("blad1").Range(Cells(x1, 12)).Copy 'copy from cell Lx1 Worksheets("blad2").Select 'go to workbook Blad2 (sheet2) Worksheets("blad2").Range("Cells(x, 3)").Paste 'paste from cell Lx1 to column C (3) row x. x = x + 1 'next row x1 = x1 + 1 'next row number = number + 1 'add 1 number until it's equal to number of days Loop End Sub Thanks in advance! Amanda W ' Our code looks like this and uses a 'for loop' attached to a command button to automate the work of entering the number of days as a single value in one worksheet and placing the complete list of values generated from the entered value into another worksheet. Private Sub CommandButton1_Click() Dim days As Integer days = Sheet1.Range("B1").Value ' we capture the value entered in cell B1 into the variable 'days' Worksheets("Sheet2").Select ' next we select the worksheet2 x = 4 'we define the row where we'll start entering data automatically y = 3 ' we define the column where the data will go automatically For i = 1 To days 'we start the loop at 1 and the values go up to the number captured in the variable Sheet2.Cells(x, y) = i ' we assign a value to cells(4,3) i. e. cell C4 automatically x = x + 1 ' the macro goes to the next row. Example: Cells(5,3) i. e. cell C5 and so on ... Next i 'looping should continue till i=days Worksheets("sheet2").Range("D4").Select ' we now remain in sheet2 but select cell D4 to continue our work End Sub 
Home  More Excel (VBA) Training Videos 