+ Reply to Thread
Results 1 to 3 of 3

Need input box in working code

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    07-28-2012
    Location
    madrid
    MS-Off Ver
    Excel 2010 at work excel 2016
    Posts
    1,102

    Need input box in working code

    Hi I have below code thanks to Alphafrog, which works brilliant I need to insert a msg if some criteria is is not full filled.

    Below is the working code and I need to insert this criteria.

    Please have a look and see if you can help

    Sincerely
    Abjac

    Creteria to be inserted in the code is

    If .Cells(i, "A") = "758" And .Cells(i, "B") = "03" And .Cells(i, "P") = "" Then .
    MsgBox "You need to insert the account name for country code 758 ledger 03"
    Exit sub
    Here is the code I would like to merge this criteria with.

    Sub alphafrog()
        Dim i As Long, j As Long, arrEQ As Variant, iCell As Range
        
        'Number of Dots in cells E to Q
        arrEQ = Array(5, 8, 12, 2, 6, 6, 1, 1, 3, 11, 42, 35, 6)
        
        Sheets("Robot").Activate
        With Sheets("Robot")
            
            For i = 3 To .Cells(.Rows.Count, "A").End(xlUp).Row
            
                If WorksheetFunction.CountBlank(.Range("A" & i & ":AQ" & i)) <> 43 Then
                
                    '32 dots in cell R
                    If .Cells(i, "A") <> "" And .Cells(i, "B") = "03" Then
                        With .Cells(i, "R")
                            .Value = .Value & String(Application.Max(32 - Len(.Value), 0), ".")
                        End With
                    End If
                    
                    'Cells E:Q (number of dots defined in arrEQ)
                    For j = 0 To 12
                        With .Cells(i, "E").Offset(, j)
                            .Value = .Value & String(Application.Max(arrEQ(j) - Len(.Value), 0), ".")
                        End With
                    Next j
                    
                    '3 dots in cells S:AQ
                    For Each iCell In .Range("S" & i & ":AQ" & i)
                        iCell = iCell & String(Application.Max(3 - Len(iCell), 0), ".")
                    Next iCell
                    
                End If
            Next i
        End With
    End Sub

  2. #2
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Need input box in working code

    If I understand what you want correctly..:

    Sub alphafrog()
        Dim i As Long, j As Long, arrEQ As Variant, iCell As Range
        
        'Number of Dots in cells E to Q
        arrEQ = Array(5, 8, 12, 2, 6, 6, 1, 1, 3, 11, 42, 35, 6)
        
        Sheets("Robot").Activate
        With Sheets("Robot")
            
            For i = 3 To .Cells(.Rows.Count, "A").End(xlUp).Row
            
                If WorksheetFunction.CountBlank(.Range("A" & i & ":AQ" & i)) <> 43 Then
                    If .Cells(i, "A") = "758" And .Cells(i, "B") = "03" And .Cells(i, "P") = "" Then .
                        MsgBox "You need to insert the account name for country code 758 ledger 03" & vbcrlf & vbcrlf & "Row: " & i
                        '.Cells(i, "P").Select  ' Jump to cell -- optional
                        Exit sub
                    End If
    
                    '32 dots in cell R
                    If .Cells(i, "A") <> "" And .Cells(i, "B") = "03" Then
                        With .Cells(i, "R")
                            .Value = .Value & String(Application.Max(32 - Len(.Value), 0), ".")
                        End With
                    End If
                    
                    'Cells E:Q (number of dots defined in arrEQ)
                    For j = 0 To 12
                        With .Cells(i, "E").Offset(, j)
                            .Value = .Value & String(Application.Max(arrEQ(j) - Len(.Value), 0), ".")
                        End With
                    Next j
                    
                    '3 dots in cells S:AQ
                    For Each iCell In .Range("S" & i & ":AQ" & i)
                        iCell = iCell & String(Application.Max(3 - Len(iCell), 0), ".")
                    Next iCell
                    
                End If
            Next i
        End With
    End Sub
    多么想要告诉你 我好喜欢你

  3. #3
    Forum Contributor
    Join Date
    07-28-2012
    Location
    madrid
    MS-Off Ver
    Excel 2010 at work excel 2016
    Posts
    1,102

    Re: Need input box in working code

    HI Millz it works just perfect thanks allot have a nice day.

    Sincerely

    Abjac

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Code not working on a input box if inputted character length is 5
    By jshaw82 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 07-09-2013, 12:16 PM
  2. [SOLVED] Remove input boxes in working code
    By abjac in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-15-2013, 07:45 PM
  3. User input variables Code not working
    By LeapingLizard in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-28-2012, 10:58 PM
  4. Changing code from Manual input to Calculated input
    By Gertheking in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-16-2012, 11:41 AM
  5. [SOLVED] Pause code, wait for input, no input received, carry on with the code
    By johnd@arm.co.uk in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-29-2005, 08:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1