| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Goal seek - column reference
Sub Goalseek()
' Range("Y84").Goalseek Goal:=Range("AL84"), ChangingCell:=Range("Y50") Range("Y85").Select Range("Y85").Goalseek Goal:=Range("AL85"), ChangingCell:=Range("Y51") Range("Y86").Select Range("Y86").Goalseek Goal:=Range("AL86"), ChangingCell:=Range("Y52") End Sub In above macro column Y can vary - i.e it E to X. I therefore would like to have the column matched against corresponding Numbers e.g 1 would be equivalent to column E, 2 would be equivalent to column F & so on. I have cell AL83 which gives the required column "number" How can i amend the above macro to link it with the value in cell AL83 & proceed with the goal seek Thxs |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Goal seek - column reference
Al,
Basically, you need to replace Range("CellAddress") with Cells(Row#, Col#) and pick up the column number by reading the value of cell AL83 For example, you can replace Range("Y84") with Cells(84, Range("AL83").Value + 4) So your final code might look like this: Cells(84, Range("AL83").Value + 4).GoalSeek Goal:=Range("AL84"), ChangingCell:=Cells(50, Range("AL83").Value + 4) Not sure if your Goal changes each time or not.... HTH, Bernie MS Excel MVP "al" wrote in message ... Sub Goalseek() ' Range("Y84").Goalseek Goal:=Range("AL84"), ChangingCell:=Range("Y50") Range("Y85").Select Range("Y85").Goalseek Goal:=Range("AL85"), ChangingCell:=Range("Y51") Range("Y86").Select Range("Y86").Goalseek Goal:=Range("AL86"), ChangingCell:=Range("Y52") End Sub In above macro column Y can vary - i.e it E to X. I therefore would like to have the column matched against corresponding Numbers e.g 1 would be equivalent to column E, 2 would be equivalent to column F & so on. I have cell AL83 which gives the required column "number" How can i amend the above macro to link it with the value in cell AL83 & proceed with the goal seek Thxs |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Goal seek - column reference
On Jan 26, 12:29 am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote: Al, Basically, you need to replace Range("CellAddress") with Cells(Row#, Col#) and pick up the column number by reading the value of cell AL83 For example, you can replace Range("Y84") with Cells(84, Range("AL83").Value + 4) So your final code might look like this: Cells(84, Range("AL83").Value + 4).GoalSeek Goal:=Range("AL84"), ChangingCell:=Cells(50, Range("AL83").Value + 4) Not sure if your Goal changes each time or not.... HTH, Bernie MS Excel MVP "al" wrote in message ... Sub Goalseek() ' Range("Y84").Goalseek Goal:=Range("AL84"), ChangingCell:=Range("Y50") Range("Y85").Select Range("Y85").Goalseek Goal:=Range("AL85"), ChangingCell:=Range("Y51") Range("Y86").Select Range("Y86").Goalseek Goal:=Range("AL86"), ChangingCell:=Range("Y52") End Sub In above macro column Y can vary - i.e it E to X. I therefore would like to have the column matched against corresponding Numbers e.g 1 would be equivalent to column E, 2 would be equivalent to column F & so on. I have cell AL83 which gives the required column "number" How can i amend the above macro to link it with the value in cell AL83 & proceed with the goal seek Thxs Have change the macro as you suggested - but it does not work - can you or someone else help pls Sub Goalseekoffset() ' Cells(84, Range("AL83").Value + 4).Goalseek Goal:=Range("AL84"), ChangingCell:=Cells(50, Range("AL83").Value + 4) Cells(85, Range("AL83").Value + 4).Goalseek Goal:=Range("AL85"), ChangingCell:=Cells(51, Range("AL83").Value + 4) Cells(86, Range("AL83").Value + 4).Goalseek Goal:=Range("AL86"), ChangingCell:=Cells(52, Range("AL83").Value + 4) End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Goal seek - column reference
On Jan 26, 1:20 am, al wrote:
On Jan 26, 12:29 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Al, Basically, you need to replace Range("CellAddress") with Cells(Row#, Col#) and pick up the column number by reading the value of cell AL83 For example, you can replace Range("Y84") with Cells(84, Range("AL83").Value + 4) So your final code might look like this: Cells(84, Range("AL83").Value + 4).GoalSeek Goal:=Range("AL84"), ChangingCell:=Cells(50, Range("AL83").Value + 4) Not sure if your Goal changes each time or not.... HTH, Bernie MS Excel MVP "al" wrote in message ... Sub Goalseek() ' Range("Y84").Goalseek Goal:=Range("AL84"), ChangingCell:=Range("Y50") Range("Y85").Select Range("Y85").Goalseek Goal:=Range("AL85"), ChangingCell:=Range("Y51") Range("Y86").Select Range("Y86").Goalseek Goal:=Range("AL86"), ChangingCell:=Range("Y52") End Sub In above macro column Y can vary - i.e it E to X. I therefore would like to have the column matched against corresponding Numbers e.g 1 would be equivalent to column E, 2 would be equivalent to column F & so on. I have cell AL83 which gives the required column "number" How can i amend the above macro to link it with the value in cell AL83 & proceed with the goal seek Thxs Have change the macro as you suggested - but it does not work - can you or someone else help pls Sub Goalseekoffset() ' Cells(84, Range("AL83").Value + 4).Goalseek Goal:=Range("AL84"), ChangingCell:=Cells(50, Range("AL83").Value + 4) Cells(85, Range("AL83").Value + 4).Goalseek Goal:=Range("AL85"), ChangingCell:=Cells(51, Range("AL83").Value + 4) Cells(86, Range("AL83").Value + 4).Goalseek Goal:=Range("AL86"), ChangingCell:=Cells(52, Range("AL83").Value + 4) End Sub sorry pals - it's working - got the input wrong in cell AL83 - thxs |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Goal Seek with dynamic Goal Seek | Excel Programming | |||
| goal seek reference | Excel Programming | |||
| goal seek circular reference | Excel Discussion (Misc queries) | |||
| How to set the "to value" in GOAL SEEK as a cell reference? | Excel Worksheet Functions | |||
| Goal Seek - reference a cell for "To value" field? | Excel Worksheet Functions | |||
Hybrid Mode
