[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 642
  • Last Modified:

Excel VBA help

Hello EE,
I hope some one can help me with this!
I have a Work Order sheet where I grab a sequential number from a Tallysheet by pressing a button and it runs this macro:

Sub New_Number()
'
' New_Number Macro
    If Len(Range("B3").Value & Range = 0 Then Exit Sub
    Sheets("Tallysheet").Select
    Rows("3:3").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A4").Select
    Selection.AutoFill Destination:=Range("A3:A4"), Type:=xlFillDefault
    Range("A3:A4").Select
    Range("A3").Select
    Selection.Copy
    Sheets("FT CC (2)").Select
    Range("B3").Select
    Sheets("Tallysheet").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("FT CC (2)").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
End Sub

The problem I am having is that the user can press the button more than once ending up with a blank number. The first line is my attempt to check B3, but I get a syntax error.
So I would like the VB to check "B3" for any number. If there is a number there then it will have a popup box and then exit the code.

IF you need more info please post.
Thanks in advance.
Rick
0
rrbad
Asked:
rrbad
  • 2
1 Solution
 
Farzad AkbarnejadCommented:
Change the first line of code to:

If Len(Trim(Range("B3").Value)) = 0 Then
     MsgBox "Your message is here"
     Exit Sub
End if
0
 
rrbadAuthor Commented:
FarzedA,
Thank You for the quick response. It is very close! It needs to check B3 for a number. If there is a number in the cell then I need a text box. Right now it is checking for a blank cell.
Rick
0
 
Farzad AkbarnejadCommented:
Hi,
Try this:

If IsNumeric(Trim(Range("B3").Value)) Then
     MsgBox "Your message is here"
     Exit Sub
End if
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now