Solution for Linked cell delete in macro
Posted by Beginner Bob on September 01, 2000 11:38 AM
I have part of a macro that states
TB = Range("C34").Value
If Not IsNumeric(TB) Then
MsgBox "PLEASE ENTER A NUMBER.", vbExclamation, "ERROR"
TubTest 'name of sub, start over
End If
Range("C34").Select
If Not Range("C34").Value > 0 Then
MsgBox "PLEASE ENTER A NUMBER GREATER THAN 0.", vbExclamation, "ERROR"
TubTest 'name of sub, start over
End If
Range("B34").Select
ActiveCell.FormulaR1C1 = "=RC[1]*50"
Range("C34").Select
Selection.ClearContents
Obviously this doesn't work because the formula for B34 will give a '0'
once C34 is cleared. Is there a way to change the value in B34 to a constant
instead of the formula (after it's entered into B34)?
Ex.- If 5 is entered in inputbox, B34 becomes '250' instead of =C34*50
Please help. Thanks
Posted by Ivan Moala on September 02, 0100 12:32 AM
Re: That's perfect for text, but...
Try this instead
Public Sub TubTest1()
GetVal:
Range("C34").Value = Application.InputBox("ENTER AMOUNT OF TUBS IN PLAN.", "TUB AMOUNT", Type:=1)
If Range("C34").Value = "False" Then Range("C34") = "": End
If Range("C34").Value < 101 And Range("C34").Value > 0.9 Then
Range("B34") = Range("C34").Value * 50
Range("C34").ClearContents
Else
Range("C34").ClearContents
MsgBox "Number must be between 1 - 100"
GoTo GetVal
End If
End Sub
Ivan
Posted by Ivan Moala on September 01, 0100 12:11 PM
Would
Selection.Value = tb * 50
IN PLACE OF
ActiveCell.FormulaR1C1 = "=RC[1]*50"
Work ??
Ivan
Posted by Bob on September 01, 0100 2:01 PM
This works thanks, but now if I test it entering any combo of letters first into
the inputbox, it gives me the first error message (which it should), then
restarts sub (which it should), but when I enter 5 for example the second
time around it just enters '0' into B34. I guess something's wrong
with the rest of my code. Can you, or anyone figure this out, and maybe
help me clean it up? Thanks. Here's the updated code (I took out TB):
Public Sub TubTest()
Range("C34").Value = InputBox("ENTER AMOUNT OF TUBS IN PLAN.", "TUB AMOUNT")
Range("C34").Select
If Not IsNumeric(Range("C34").Value) Then
MsgBox "PLEASE ENTER THE NUMBER OF TUBS IN THIS PLAN.", vbExclamation, "ERROR"
TubTest
End If
If Not Range("C34").Value > 0 Then
MsgBox "PLEASE ENTER A NUMBER GREATER THAN 0.", vbExclamation, "ERROR"
TubTest
End If
Range("B34").Select
Selection.Value = Range("C34").Value * 50
Range("C34").Select
Selection.ClearContents
End Sub
Posted by Bob on September 02, 0100 3:45 PM
That's exactly what I needed, thanks Ivan.
Public Sub TubTest1()
Posted by Ivan Moala on September 01, 0100 7:17 PM
You could try this Mod. version and see how it works.
It uses the Application inputbox instead to weed
out Data types and the recursion error in starting
up the routine again. Note: in your previous code
you could have put in Goto (and your refer.) instead
of recalling the routine again.
Public Sub TubTest1()
Range("C34").Value = Application.InputBox("ENTER AMOUNT OF TUBS IN PLAN.", "TUB AMOUNT", Type:=1)
If Range("C34").Value = False Then Range("C34") = "": End
Range("B34") = Range("C34").Value * 50
Range("C34").ClearContents
End Sub
HTH
Ivan
Posted by Bob on September 01, 0100 9:29 PM
That's perfect for text, but...
What do I add to:
Public Sub TubTest1()
Range("C34").Value = Application.InputBox("ENTER AMOUNT OF TUBS IN PLAN.", "TUB AMOUNT", Type:=1)
If Range("C34").Value = False Then Range("C34") = "": End
Range("B34") = Range("C34").Value * 50
Range("C34").ClearContents
End Sub
to make sure the number is between 1 and 100? Mainly, I don't want
negative numbers allowed. Thanks Ivan.
