Modifying a formula from VBA

[cappy2112]

I'm very new to VBA and never really used Excel for calculations.
That said, I've managed to get some VBA scripting working in a workbook, with the help of a few people from this forum.
https://www.mrexcel.com/forum/excel-questions/998055-improving-visual-basic-applications-execution-speed-when-writing-excel-worksheet.html#post4791407


I'm at the last step of this project (for now, at least), and I'm pretty excited that it's going so well.

One worksheet has a range of cells with this formula ='rTAB1'!H78/'rTAB2'!H78-1
(obviously, the formula updates relatively for each cell)

The "TAB1" refers to a data set on one worksheet, "TAB2" refers to a data set in the same workbook, but on a different worksheet.

I've written a VBA subroutine to populate two combo boxes with the names of all tabs/worksheets, in the workbook. The combo boxes
provide a simple way for the user to select two worksheets, in order to perform an operation.
The user will select 1 worksheet in each combo box, then click on a button to perform the next operation.

Then, my VBA code needs to update the formula in all of the cells, so that the formula uses the worksheet names from the combo boxes, selected by the user. I know how to get the selected Item in the combo boxes, but I don't know how to put that string into the formula for a cell, then copy that formula to the rest of the desired cell range.

I could use some help modifying the formula, then copying it to the rest of the cell range.

Apologies if I'm using incorrect terminology here. As I mentioned, I'm new to VBA and don't use Excel.

Thanks

 

[Carpaccio]

Hi Cappy 2112

I've assumed that you first combobox is called ComboBox1, your second ComboBox2 and your OK button is called OKButton.
I've also assumed that the range for "all of the cells," is just 2 cells (H79 to H80)

You might try code like the following in your form, that will run when the User clicks on OK.

Private Sub OKbutton_Click()
Dim Choice1 As String
Dim Choice2 As String


    Choice1 = ComboBox1.Value
    Choice2 = ComboBox2.Value
    
    Worksheets(Choice1).Cells(78, 8) = "='" & Choice1 & "'!H78/'rTAB2'!H78-1"
    Cells(78, 8).Copy
    Range("H79:H80").Select
    ActiveSheet.Paste
    
    Worksheets(Choice2).Cells(78, 8) = "='" & Choice2 & "'!H78/'rTAB2'!H78-1"
    Cells(78, 8).Copy
    Range("H79:H80").Select
    ActiveSheet.Paste
    
End Sub

 

[cappy2112]

Hi Cappy 2112

I've assumed that you first combobox is called ComboBox1, your second ComboBox2 and your OK button is called OKButton.
I've also assumed that the range for "all of the cells," is just 2 cells (H79 to H80)

You might try code like the following in your form, that will run when the User clicks on OK.

Private Sub OKbutton_Click()
Dim Choice1 As String
Dim Choice2 As String


    Choice1 = ComboBox1.Value
    Choice2 = ComboBox2.Value
    
    Worksheets(Choice1).Cells(78, 8) = "='" & Choice1 & "'!H78/'rTAB2'!H78-1"
    Cells(78, 8).Copy
    Range("H79:H80").Select
    ActiveSheet.Paste
    
    Worksheets(Choice2).Cells(78, 8) = "='" & Choice2 & "'!H78/'rTAB2'!H78-1"
    Cells(78, 8).Copy
    Range("H79:H80").Select
    ActiveSheet.Paste
    
End Sub

Thanks!

I'm trying this now.
Can I use the ROW_CELL nomenclature C8, instead of what you've shown?
I also have a named range assoicated with that cell DATA_START.
How would I use that instead of cells(x,y) ?

 

[Carpaccio]

Hi Cappy2112
you can try the following to use Range instead of Cells.

And yes, you can replace a range with a named range, eg Range("H78").Select becomes Range("DATA_START").Select

But you can not have more then one range called DATA_START in the workbook, so the macro won't be able to select a worksheet according to the combobox, you'd then have to have a different range for each sheet, and select the right range according to the combobox. This can be done, but I haven't

Private Sub OKbutton_Click()
Dim Choice1 As String
Dim Choice2 As String


    Choice1 = ComboBox1.Value
    Choice2 = ComboBox2.Value
    
    Worksheets(Choice1).Range("H78").Value = CStr("='" & Choice1 & "'!H78/'rTAB2'!H78-1")
    
    Range("H78").Copy
    Range("H78:H80").Select
    ActiveSheet.Paste
    
    Worksheets(Choice2).Range("H78").Value = CStr("='" & Choice2 & "'!H78/'rTAB2'!H78-1")
    Range("H78").Copy
    Range("H78:H80").Select
    ActiveSheet.Paste
    
End Sub

 

[cappy2112]

Thanks- I was able to get my code working.
I've modified the forumal based on the combo box selections, and updated the entire range of cells with the new formula.