Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using result of user input?
Can anyone please, please help?
I need to write a macro that will ask the user what week it is, then find that corresponding week number on a spreadsheet and then put a load of formula into the blank cells in that column from rows 9 down to 360. I can get the macro to ask for the output and find the cells, but I can't seem to get the last part to work. The code I'm using is below, can anyone tell where I'm going wrong? Dim intC As Integer MyValue3 = Application.InputBox("What week is it?") Cells(3, 3) = MyValue3 Range("o7:r7").Find(What:=MyValue3).Activate intC = ActiveCell.Column Range(Cells(9, intC), Cells(360, intC)).Select Dim Cell As Range For Each Cell In Selection If Cell.Value < "" Then Cell.Offset(0, 11).Value = "=INDEX('Raw Data'!R1:R600,MATCH((Template!RC[-13]&Template!RC[-12]),'Raw Data'!C1,0),MATCH(R7C,'Raw Data'!R3,0))" Range("O11").Select End If Next Cell End Sub --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using result of user input?
as you're entering formula not value, you need to change
this Cell.Offset(0, 11).Value = to this Cell.Offset(0, 11).FormulaR1C1 = also Range("o7:r7").Find(What:=MyValue3).Activate intC = ActiveCell.Column try intC =Range("o7:r7").Find(What:=MyValue3).Column It would be better to trap if a find doesn't return an answer... Set cell = Range("o7:r7").Find(What:=MyValue3) If NOT Cell is nothing then intC= cell.column ' your loop goes here For Each Cell In Range(Cells(9, intC), _ Cells(360, intC)) If Cell.Value < "" Then Cell.Offset(0, 11).Value = "=INDEX('Raw Data'!R1:R600,MATCH((Template!RC[-13] & _ Template!RC[- 12]),'Raw Data'!C1,0),MATCH(R7C,'Raw Data'!R3,0))" Range("O11").Select End If next end if Note that there's no need to activate or select cells to use them Patrick Molloy Microsoft Excel MVP -----Original Message----- Can anyone please, please help? I need to write a macro that will ask the user what week it is, then find that corresponding week number on a spreadsheet and then put a load of formula into the blank cells in that column from rows 9 down to 360. I can get the macro to ask for the output and find the cells, but I can't seem to get the last part to work. The code I'm using is below, can anyone tell where I'm going wrong? Dim intC As Integer MyValue3 = Application.InputBox("What week is it?") Cells(3, 3) = MyValue3 Range("o7:r7").Find(What:=MyValue3).Activate intC = ActiveCell.Column Range(Cells(9, intC), Cells(360, intC)).Select Dim Cell As Range For Each Cell In Selection If Cell.Value < "" Then Cell.Offset(0, 11).Value = "=INDEX('Raw Data'!R1:R600,MATCH((Template!RC[-13]&Template!RC[- 12]),'Raw Data'!C1,0),MATCH(R7C,'Raw Data'!R3,0))" Range("O11").Select End If Next Cell End Sub --- Message posted from http://www.ExcelForum.com/ . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using result of user input?
Thanks Patrick, but this still doesn't seem to be picking up the cell
range that I need. The code that I'm using now is: MyValue3 = Application.InputBox("What week is it?") Cells(3, 3) = MyValue3 intC = Range("o7:r7").Find(What:=MyValue3).Column Set Cell = Range("o7:r7").Find(What:=MyValue3) If Not Cell Is Nothing Then intC = Cell.Column For Each Cell In Selection If Cell.Value < "" Then Cell.Offset(0, 11).Value = "=INDEX('Raw Data'!R1:R600,MATCH((Template!RC[-13]&Template!RC[-12]),'Raw Data'!C1,0),MATCH(R7C,'Raw Data'!R3,0))" End If Next Cell End If If I set the range as d9:D360 before the loop, then it runs fine. But I cannot for the life of me get the code right to pick the range as a result of the input. For example, if the input is week 47, it does not do anything, even though week 47 is in the range o7:r7. Can anyone please help me? --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
input vs. result | Excel Discussion (Misc queries) | |||
Prompt user for input and utilize that input | Excel Worksheet Functions | |||
Have user input converted to uppercase in same cell as input? | New Users to Excel | |||
CODE to select range based on User Input or Value of Input Field | Excel Programming | |||
Get User Input | Excel Programming |