Using result of user input?

  • Thread starter Thread starter PaulSinki
  • Start date Start date
P

PaulSinki

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
 
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
Click to expand...
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
Click to expand...
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
Click to expand...
 
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?
 
Back
Top