Find cell

[Guest]

I have a spreadsheet that is filled with data quarterly.

I think I want to have the macro look for Q1, which would be via an input
box, find Q1 and begin entering the data from that point on. Any suggestions?

 

[kemal]

Hi

You don't mean you have allocated some rows for q1,q2,q3,q4 ,do you?
What is the display of your sheet ? Pls give us some more details.
Otherwise you can always use excel built in "find" which is the
fastest method.

rgds

 

[matt]

I have a spreadsheet that is filled with data quarterly.

I think I want to have the macro look for Q1, which would be via an input
box, find Q1 and begin entering the data from that point on. Any suggestions?

Clarissa,

You can do something similar to the following:

Option Explicit

Sub findQ1()

Dim response

response = InputBox("Input the corresponding quarter","Q?","Q1")
'note: you'll probably want to validate that the input text is
''Q1, Q2, Q3, or Q4.

Cells.Find(What:=response).Activate
'Find has a lot more parameters than just "What"

'then once the cell is found you can output the values by using
something
'like ActiveCell.Offset(row,col).Value = .....

End Sub

As a side note, look up the "Find" and "Search" functions in the Excel
help.

Matt

 

[Guest]

Thanks Matt,

This was helpful.

As I said the report is run quarterly, i.e. March, June, September and
December.

The report has 23 spreadsheets for each division, so the "response" will
work magnificently until the macro gets too large and then I'll have to call
another macro to run similarly for the remainder of the divisions, the user
will then have to input the current quarter again.

Is there a way, for example, that I can add an IF - like if current month =
"March" look for Q1 and so forth? Thank you

 

[matt]

Thanks Matt,

This was helpful.

As I said the report is run quarterly, i.e. March, June, September and
December.

The report has 23 spreadsheets for each division, so the "response" will
work magnificently until the macro gets too large and then I'll have to call
another macro to run similarly for the remainder of the divisions, the user
will then have to input the current quarter again.

Is there a way, for example, that I can add an IF - like if current month =
"March" look for Q1 and so forth? Thank you














- Show quoted text -

Clarissa,

In the VBE Help section, search "Select Case Statement" and
"If...Then...Else Statement," and you should find what you need. In
short, if you pull the current month in (whether from the ActiveCell
or from an InputBox), you can run the value through a Case Statement
to output what you want. The following should help lead you in the
right direction. By the way, I'm not sure what you mean by "...until
the macro gets too large..." (Don't discount the ability of Excel).

If the quarter END is Mar, Jun, Sep, and Dec (i.e. the fiscal year end
is Dec).

Option Explicit

Sub findQuarters()

Dim response
Dim findTxt
Dim monthDate

monthDate = Format(DateSerial(Year(Date), Month(Date), Day(Date)),
"mmmm")

response = InputBox("Input the current month","Current
Month",monthDate)
'note: you'll probably want to validate that the input text is valid

Select Case response
Case "January"
findTxt = "Q1"
Case "February"
findTxt = "Q1"
Case "March"
findTxt = "Q1"
Case "April"
findTxt = "Q2"
Case "May"
findTxt = "Q2"
Case "June"
findTxt = "Q2"
Case "July"
findTxt = "Q3"
Case "August"
findTxt = "Q3"
Case "September"
findTxt = "Q3"
Case "October"
findTxt = "Q4"
Case "November"
findTxt = "Q4"
Case "December"
findTxt = "Q4"
End Select

Cells.Find(What:=findTxt).Activate
'Find has a lot more parameters than just "What"
''Also, keep in mind that you need to code something that
''will work if "findTxt" is Nothing.

'then once the cell is found you can output the values by using
something
'like ActiveCell.Offset(row,col).Value = .....

End Sub