Can I write a macro to ask for a specific number?
 

I have a worksheet that has numerous columns and rows. I would like to write
a macro looking for a number then format the cell. I would like to set up a
button to do this and ask for a different number each time.
--
DMM

Can I write a macro to ask for a specific number?
 

put a button from the control toolbox toolbar on the sheet. Double click on
it to take you to the click event (or right click and select view code).

Put in code like the below pseudo code:

Private Sub Commandbutton1_Click()
dim ans as Long, rng as Range

ans = application.Inputbox("Enter an integer to format",type:=1)
set rng = cells.Find(ans)
if not rng is nothing then
rng.NumberFormat = "#,##0"
else
msgbox ans & " was not found"
End if
End Sub

now go back to excel and take the sheet out of design mode (click on the
depressed button on the control toolbox toolbar with the ruler and drawing
triangle - or their may be a separate floating toolbar with this same
button).

--
Regards,
Tom Ogilvy

"soconfused" wrote:

I have a worksheet that has numerous columns and rows. I would like to write
a macro looking for a number then format the cell. I would like to set up a
button to do this and ask for a different number each time.
--
DMM

Can I write a macro to ask for a specific number?
 

Hi Tom,

I am doing this, but it's not finding the number in the worksheet? I'm sure
I'm doing something wrong.
--
DMM


"Tom Ogilvy" wrote:

put a button from the control toolbox toolbar on the sheet. Double click on
it to take you to the click event (or right click and select view code).

Put in code like the below pseudo code:

Private Sub Commandbutton1_Click()
dim ans as Long, rng as Range

ans = application.Inputbox("Enter an integer to format",type:=1)
set rng = cells.Find(ans)
if not rng is nothing then
rng.NumberFormat = "#,##0"
else
msgbox ans & " was not found"
End if
End Sub

now go back to excel and take the sheet out of design mode (click on the
depressed button on the control toolbox toolbar with the ruler and drawing
triangle - or their may be a separate floating toolbar with this same
button).

--
Regards,
Tom Ogilvy

"soconfused" wrote:

I have a worksheet that has numerous columns and rows. I would like to write
a macro looking for a number then format the cell. I would like to set up a
button to do this and ask for a different number each time.
--
DMM

Can I write a macro to ask for a specific number?
 

I enriched the macro and tested it - worked fine for me:

Private Sub Commandbutton1_Click()
Dim ans As Long, rng As Range

ans = Application.InputBox("Enter an integer to format", Type:=1)
Set rng = Cells.Find(What:=ans, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
MsgBox ans & " found at " & rng.Address
rng.NumberFormat = "#,##0"
Else
MsgBox ans & " was not found"
End If
End Sub

If the number is produced by a formula, change xlFormulas to xlValues

If the number isn't an integer or if it is produced by a formula, make sure
you are searching for the exact number - sometimes, a number like 16 might be
stored or calculated as 15.9999999999999 as an example.

--
Regards,
Tom Ogilvy


"soconfused" wrote:

Hi Tom,

I am doing this, but it's not finding the number in the worksheet? I'm sure
I'm doing something wrong.
--
DMM


"Tom Ogilvy" wrote:

put a button from the control toolbox toolbar on the sheet. Double click on
it to take you to the click event (or right click and select view code).

Put in code like the below pseudo code:

Private Sub Commandbutton1_Click()
dim ans as Long, rng as Range

ans = application.Inputbox("Enter an integer to format",type:=1)
set rng = cells.Find(ans)
if not rng is nothing then
rng.NumberFormat = "#,##0"
else
msgbox ans & " was not found"
End if
End Sub

now go back to excel and take the sheet out of design mode (click on the
depressed button on the control toolbox toolbar with the ruler and drawing
triangle - or their may be a separate floating toolbar with this same
button).

--
Regards,
Tom Ogilvy

"soconfused" wrote:

I have a worksheet that has numerous columns and rows. I would like to write
a macro looking for a number then format the cell. I would like to set up a
button to do this and ask for a different number each time.
--
DMM

Can I write a macro to ask for a specific number?
 

Hi Tom,

I am getting the number that I want now, but it's just telling me where to
find it. I want it to find it and change the fill color to no color.
--
DMM


"Tom Ogilvy" wrote:

I enriched the macro and tested it - worked fine for me:

Private Sub Commandbutton1_Click()
Dim ans As Long, rng As Range

ans = Application.InputBox("Enter an integer to format", Type:=1)
Set rng = Cells.Find(What:=ans, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
MsgBox ans & " found at " & rng.Address
rng.NumberFormat = "#,##0"
Else
MsgBox ans & " was not found"
End If
End Sub

If the number is produced by a formula, change xlFormulas to xlValues

If the number isn't an integer or if it is produced by a formula, make sure
you are searching for the exact number - sometimes, a number like 16 might be
stored or calculated as 15.9999999999999 as an example.

--
Regards,
Tom Ogilvy


"soconfused" wrote:

Hi Tom,

I am doing this, but it's not finding the number in the worksheet? I'm sure
I'm doing something wrong.
--
DMM


"Tom Ogilvy" wrote:

put a button from the control toolbox toolbar on the sheet. Double click on
it to take you to the click event (or right click and select view code).

Put in code like the below pseudo code:

Private Sub Commandbutton1_Click()
dim ans as Long, rng as Range

ans = application.Inputbox("Enter an integer to format",type:=1)
set rng = cells.Find(ans)
if not rng is nothing then
rng.NumberFormat = "#,##0"
else
msgbox ans & " was not found"
End if
End Sub

now go back to excel and take the sheet out of design mode (click on the
depressed button on the control toolbox toolbar with the ruler and drawing
triangle - or their may be a separate floating toolbar with this same
button).

--
Regards,
Tom Ogilvy

"soconfused" wrote:

I have a worksheet that has numerous columns and rows. I would like to write
a macro looking for a number then format the cell. I would like to set up a
button to do this and ask for a different number each time.
--
DMM