Using VBA to copy/paste value from one cell to blank table

[SFTOPH]

Hi,

An example of what I am trying to accomplish is as follows:

Cell B1 is a drop down list of the variables in the Y axis of the table
Cell B2 is a drop down list of the variables in the X axis of the table
Cell B3 is the value.
Cell B4 contains a VBA button.

I am hoping to be able to click the button and the cell in the table that corresponds with the data in B1 and B2 will populate with the hard coded value in B3. And this can go on repeatedly without losing the data that was previously hard coded in the table.

Any help would be tremendously appreciated.

Thank you!

 

[baitmaster]

welcome to the board

I would use formulas. You could do it with VBA but it seems unnecessary plus you need to keep track of the original data etc.

I created a 5x5 table of data in A1:F6 with headers. Drop down (data validation) row selector in A8, column in B8, override value in C8

Then a second table in A10:F15 with same headers, and formula in B11 =IF(AND(B$10=$B$8,$A11=$A$8),$C$8,B2), copied to all cells

 

[SFTOPH]

welcome to the board

I would use formulas. You could do it with VBA but it seems unnecessary plus you need to keep track of the original data etc.

I created a 5x5 table of data in A1:F6 with headers. Drop down (data validation) row selector in A8, column in B8, override value in C8

Then a second table in A10:F15 with same headers, and formula in B11 =IF(AND(B$10=$B$8,$A11=$A$8),$C$8,B2), copied to all cells

Thanks for the response, but this doesn't do what I am looking for. Following your suggestion, it would just create the same table in A10:F15 and the number in C8 would move around the second table depending on what corresponding cell was selected based on A8 and B8. Additionally, this setup won't work for me with a pre-filled out table with numbers because I'm hoping to manually input random numbers into a rather large table, hence the need for this macro.

I'm hoping to be able to have a large table (over 100 rows and columns) where you can manually input a random number into the inputs section and it will get hard coded into the table.

 

[baitmaster]

OK I think I can see what you want but I'm unconvinced you're going about it the right way. A 100x100 table will take a long time to fill with random data if you're changing one cell at a time and have to set the row and column interfaces. I'm also slightly unclear on the requirement to keep the original data, which is why I set up a second table - you keep the original unchanged and work with the second

It's certainly possible to do what you want. Here's some ideas for you to clarify exactly what you do want:

This piece of code is one I use regularly to create random data (whole numbers up to 1000) in whatever area I have selected:

Sub randomData()
Dim rng As Range, arrData(), i As Long, j As IntegerSet rng = Selection
ReDim arrData(1 To rng.Rows.count, 1 To rng.Columns.count)
For i = LBound(arrData, 1) To UBound(arrData, 1)
    For j = LBound(arrData, 2) To UBound(arrData, 2)
        arrData(i, j) = Round(Rnd() * 1000, 0)
    Next j
Next i
rng = arrData
End Sub

This piece of code pasted in the worksheet code module will call the code above and enter a random number in any cell when you double-click on it:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
randomData
Cancel = True
End Sub

I suspect these may give you an alternative approach, but they can be adapted for your stated purpose easily enough

 

[baitmaster]

This meets my interpretation of your stated requirement

Sub writeRandom()
On Error GoTo errHandle
Range("table").Cells(WorksheetFunction.Match(Range("rowselector"), Range("rowheaders"), 0), WorksheetFunction.Match(Range("columnselector"), Range("columnheaders"), 0)) = Range("random")Exit Sub
errHandle:
MsgBox "unable to locate cell"
End Sub

You'll need to name the ranges as shown in the code