Offset the Change Range Value

[Boomer56]

I am looking for a piece of code that will change the value in a range in Excel VBA. For example I have a range named "Input1" which contains the vlaue in cell "B2".

I am looking for code which will change the value of "Input1" to the vlaue in the cell "B3", then "B4", "B5", etc.

Any thoughts to get me going would help... do I use a loop?, how does one change the value in a named range using "offset"?

General thoughts might get me going... for now I am stuck.

 

[PHV]

What do you want to do ???

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

"I am looking for code which will change the value of "Input1" to the vlaue in the cell "B3", then "B4", "B5", etc."

dim r as range
for each r in range("B3:B5")
  [Input1] = r.value
next

but you'll never see anything except the last value.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Boomer56]

I have code which sets the value of range "Input1" to the vlaue in "B2".

I would like to add code which will change the value of range "Input1" to the contents of "B3", call/run another macro, then change the value of range "Input1" to the contents of "B4" run the macro, and so on until there is nothing in the cell "Bx".

 

[PHV]

What have you tried so far and where in your code are stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Just add a call to the macro you want to run in the code I posted.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Boomer56]

thanks... that get's me going...

 

[Boomer56]

Quick Question - how do I run the code to change the value in a named range on multiple named ranges. I tried the code below ( and other variations) with no luck).

Sub
Dim v1 As Range
Dim v2 As Range

For Each v1 In Range("B3:B14")
[Input1] = v1.Value

For Each v2 In Range("c3:c14")
[Input2] = v2.Value

Next

End Sub

 

[SkipVought]

Please use TGML tags. If you don't know what that is. search for "Process TGML" on this page.

Sub TEST
    Dim v1 As Range
        
    For Each v1 In Range("B3:B14")
      [Input1] = v1.Value
    
      [Input2] = v1[b].offset(0,1)[/b].Value
    
    Next

End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Boomer56]

I have worked out the code to write the values as described above, but now I am stuck with the answers.

I need to write a value to a range ("Output1"), then change the location of "Output1" by one row (down). Thus my code can write the new value to "Output1", one row lower.

I tried:

Range("Output1").Value = Range("Output1").Offset(1, 0).Value

But this erases my value.

Any help is appreciated.

 

[PHV]

What is your actual code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Range("Output1")[b].offset(Range("Output1").currentregion.rows.count+1)[/b].Value = Range("Output1").Offset(1, 0).Value

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Boomer56]

I am still stuck. I ahve provided the entrie code below. My last issues is that I want to change the refence to "Output1" and "Output2" after each pass through the calculator, so that the anser are written to O22, the O33, then O44, and so on.

Thansk to your help, I am close, but not quite there.

Sub Table_Calc()

'Turn Off Scree Updating
Application.ScreenUpdating = False

ActiveWorkbook.Names.Add Name:="Output1", RefersToR1C1:="='Data Table'!R2C15"
ActiveWorkbook.Names.Add Name:="Output2", RefersToR1C1:="='Data Table'!R2C16"


'Determine Row onto which apply Calcs
Dim v1 As Range
For Each v1 In Range("B2:B14")
[Input1] = v1.Value
[Input2] = v1.Offset(0, 1).Value
[Input3] = v1.Offset(0, 2).Value
[Input4] = v1.Offset(0, 3).Value
[Input5] = v1.Offset(0, 4).Value
[Input6] = v1.Offset(0, 5).Value
[Input7] = v1.Offset(0, 6).Value
[Input8] = v1.Offset(0, 7).Value
[Input9] = v1.Offset(0, 8).Value
[Input10] = v1.Offset(0, 9).Value
[Input11] = v1.Offset(0, 10).Value
[Input12] = v1.Offset(0, 11).Value
[Input13] = v1.Offset(0, 12).Value
[Input14] = v1.Offset(0, 13).Value
[Input15] = v1.Offset(0, 14).Value

'Transfer inputs to Calculator

Sheets("Calculator").Range("D3").Formula = Range("Input1").Value
Sheets("Calculator").Range("D4").Formula = Range("Input2").Value
Sheets("Calculator").Range("D5").Formula = Range("Input3").Value
Sheets("Calculator").Range("D6").Formula = Range("Input4").Value
Sheets("Calculator").Range("D9").Formula = Range("Input5").Value
Sheets("Calculator").Range("D10").Formula = Range("Input6").Value
Sheets("Calculator").Range("D11").Formula = Range("Input7").Value
Sheets("Calculator").Range("D12").Formula = Range("Input8").Value
Sheets("Calculator").Range("I3").Formula = Range("Input9").Value
Sheets("Calculator").Range("I4").Formula = Range("Input10").Value
Sheets("Calculator").Range("I5").Formula = Range("Input11").Value
Sheets("Calculator").Range("I6").Formula = Range("Input12").Value
Sheets("Calculator").Range("I22").Formula = Range("Input13").Value

'Run Calculator
Sheets("Calculator").Select
FetchModel

'Write Values from Calculator to Data Table Base Row
Sheets("Data Table").Select
Range("Input14").Value = Sheets("Calculator").Range("G12").Value
Range("Input15").Value = Sheets("Calculator").Range("I12").Value

'Write Output Values into Table
Range("Output1").Value = Range("Input14").Value
Range("Output2").Value = Range("Input15").Value

'Change Output range down one row
Range("Output1").Offset(Range("Output1").CurrentRegion.Rows.Count + 1).Value = Range("Output1").Offset(1, 0).Value
Range("Output2").Offset(Range("Output2").CurrentRegion.Rows.Count + 1).Value = Range("Output2").Offset(1, 0).Value

Next

End Sub