Results 1 to 6 of 6

Thread: Macro for if cell A2 has text then cell B2 must have numeric value.

  1. #1
    Junior Member
    Join Date
    Mar 2013
    Posts
    6
    Rep Power
    0

    Macro for if cell A2 has text then cell B2 must have numeric value.

    Hello all.

    I am new around here and must say that I am so and so with excel formulas with help of Google.

    Macros and VBA is where I could really need a helping hand as I am zero at it.

    I am having the following problem;

    On an invoice sheet I have created I have column A2 to A32 for items description and its formatted for text. On column B2 to B32 which is for the quantity which is in numeric.

    Now the items description are there by way of a drop down list and the prices etc are by ways of vlookup.

    Now my problem is if I have selected an item say A2 I want to make sure B2 has got a numeric value as if I don't this will not produce a total as it depends on the quantity value.

    In all;

    Column A
    From A2 to A32 = Items description

    Column B
    From B2 to B32 = Quantity of items (value is calculate according to the quantity value) Now you can see the problem if I select an item but have no quantity ahead of it.

    So, macro is to look at those cells in A2 to A32 or to all cells that contain data (text) within this range and for each line that does contain data, the same line in column B must contain a numeric value, if not then stop macro.

    A good thing would be to point the focus to the cell that needs a value.

    It might be to much to ask but macro could be in a way that it could even tell user what cell is empty but I can do without that.

    Many Thanks in advance and much appreciated for all possible help out there.

    Regards,
    Albert

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Hi Albert,

    Welcome to ExcelFox!!

    What you are asking can be achievable by tweaking the b2 formula.

    in b2

    =if(len(a2),iferror(yourvlookupformula,"Not found"),"")

    it checks a2 and if a2 has value in it, performs vlookup and if it doesn't find a value to return, gives "not found" otherwise returns the found value. Now if a2 remains blank, b2 will also remain blank.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Junior Member
    Join Date
    Mar 2013
    Posts
    6
    Rep Power
    0
    vlookup formula is not in B2, only in other fields such as units and prices and vat.

    B column is purely quantity and it can be 1 or 5.
    Last edited by Admin; 03-08-2013 at 12:17 PM. Reason: removed quote

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Hi

    Please do not quote the entire message

    OK. Put this code in ThisWorkbook module (right click on ThisWorkbook)

    Code:
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        
        Dim r       As Range
        
        Const MySheet = "Sheet1"          '<<<<< adjust to suit
        Const MyRange = "A2:B32"          '<<<<< adjust to suit
        
        Set r = Worksheets(MySheet).Range(MyRange)
        
        If Application.WorksheetFunction.Count(r.Columns(2)) < _
            Application.WorksheetFunction.CountA(r.Columns(1)) Then
            MsgBox "Qty missing!", vbInformation
            On Error Resume Next
            Application.Goto r.Columns(2).SpecialCells(4)
            On Error GoTo 0
            Cancel = True
        End If
        
    End Sub
    Note: adjust the sheet name and the range accordingly.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Junior Member
    Join Date
    Mar 2013
    Posts
    6
    Rep Power
    0
    Hi.

    I have tested your macro and works to an extent.

    However, the following happened; ranges were A10 to B20

    Cells
    A10=text B10=1
    A11=text B11=1
    A12="empty cell B12=1
    A13=text B13="empty cell"

    and macro has failed to pick up empty cell B13.

    I must say that this has been the hardest one to get any sort of info on the internet and I have googled but I am not program minded so I might even be looking for the wrong thing.

    As I tend not to be clear with what I need sometimes I'll have it explained once again,

    I have an invoice spreadsheet and B (B10 to B45) column is for QT which stands for quantity.

    Column A (A10 to A45) is for item description and item is selected by a drop down list.

    Columns C and D are for UOM (Unit Of Measure) and Price which are automatically fill with a vlookup formula that looks for the item selected in column A.

    The macro or VB code I am after is to do the following;

    Range A10 to A45, for every cell with an item or data/text if you like, then the equivalent line in column B must have quantity or in other words a numeric value.

    And this is repeated throughout range A10 to A45. I was thinking of macro move on to next step when it finds an empty cell within this range but the problem is that my brother might skip a line in between and then it all goes bad once again.

    One nice thing to have in place would be to have the macro popping up an inputBox when it does come across an empty cell in B column for the range in question and asks to input quantity in the cell but if I can get the first bit working as intended then I am happy.

    I have tried to persuade my brother to purchase QB2013PRO, which is what I have for my small part time business and works as a treat, but hey he's just starting so I kind of understand him.

    Many thanks for all the help and please lets make this work together. Well, I am not included obviously hence the reason I am here.

    A very nice weekend to all.

    Cheers.
    Albert

  6. #6
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Hi

    try

    Code:
    Option Explicit
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        
        Dim r       As Range
        Dim k, i    As Long
        Dim msg     As String
        
        Const MySheet = "Sheet1"          '<<<<< adjust to suit
        Const MyRange = "A10:B45"          '<<<<< adjust to suit
        
        Set r = Worksheets(MySheet).Range(MyRange)
        
        If Application.WorksheetFunction.CountA(r) Then
            k = r.Value2
            msg = "Quantity missing in the following cell(s)"
            For i = 1 To UBound(k, 1)
                If Len(k(i, 1)) Then
                    If Len(k(i, 2)) = 0 Then
                        msg = msg & vbLf & vbTab & r.Cells(i, 2).Address(0, 0)
                    End If
                End If
            Next
            If InStr(1, msg, Chr(10)) Then
                MsgBox msg, vbInformation
                Cancel = True
            End If
        End If
        
    End Sub
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Replies: 2
    Last Post: 05-30-2013, 07:28 PM
  2. Replies: 8
    Last Post: 04-29-2013, 08:36 PM
  3. Storing Text Value From Cell To Array
    By marreco in forum Excel Help
    Replies: 2
    Last Post: 02-15-2013, 01:11 AM
  4. Last Filled Cell Having Text
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 5
    Last Post: 07-18-2011, 02:33 PM
  5. Excel Macro Functions (GET.CELL)
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 1
    Last Post: 05-17-2011, 08:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •