VBA: Changing a range of variables repeatedly

December 5, 2011 at 08:46:14
Specs: N/A
Hi there,

I have a problem in excel that i only seem to be able to solve, making a macro in VBA.

I have two different Workbooks, where the first workbook "Be" is consisting a large Mathematical model. In the other workbook, i have a so called "Sampling", where 5 of the input variables for the mathematical model varies in each step.

For each step (row) i want the macro to insert the values for the variable and return the result for each step in "sheet2" of the Workbook "Sampling".

I hope that my explanation is understandable.

See More: VBA: Changing a range of variables repeatedly

Report •

December 5, 2011 at 12:03:29
I'm not sure what you are asking for.

Are you asking for the complete macro to perform the task you have described or are you asking for some help with codeing the section where the variables are pulled from one sheet and used for the calculations in another?

In either case, we would need a lot more information before we could offer any suggestions.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

December 15, 2011 at 04:00:10
Hi again,

Sorry for the confusing question!

I found a solution to solve it, however i just have a little problem. A shortend version of my code is shown below. Right now the code is repeating itself 32 times, because i have 32 different variables. But i just want it to repeat itself untill an empty Cell, so that i dont have change the code when the number of variables changes.

Can i do that without using a "DoLoop", and just use "For... To" ?

Option Explicit

Sub Insert_varibles2()

    'Open workbook with Model "Be05_TEST2"
    Workbooks.Open ("C:\UA SA\Be05_TEST2")

    Dim i As Integer
    For i = 2 To 32
    'Input varibles
    Workbooks("Be05_TEST2").Sheets("Hoved").Cells(511, 3).Value = 
    'Output varibles
        'Energiramme - BR10
    ThisWorkbook.Sheets("Output").Cells(i, 1).Value = Workbooks("Be05_TEST2").Sheets("RESULTAT").Cells(9, 17).Value
    Workbooks("Be05_TEST2").Close SaveChanges:=False

End Sub

Report •

December 15, 2011 at 09:19:16
You should be able to set the i variable to the last row with data in Column A as follows:

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

This tells VBA to go down to the bottom of Column A and then look up until it sees a cell with data, which will be the last cell with data in that Column.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

Ask Question