Consulting

Results 1 to 8 of 8

Thread: Sleeper: VBA Script Resources

  1. #1
    VBAX Regular
    Joined
    Sep 2004
    Posts
    8
    Location

    Sleeper: VBA Script Resources

    Dear all,

    My very first post here...

    Would like to check with you guys on below items:

    1. Is there any thread or link in this forum that I can get some samples of VBA script on different topics basis?

    2. Looking for a sample of VBA script which is doing the basic function of "Clash Checking of New Input Data Line with Existing Line Input In A Ledger Form". Anyone has any idea?

    Cheers...

  2. #2
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Hi 7 Welcome to VBAX!!

    We have an excellent resource library here at VBAX we like to call our Knowledge Base. The link is at your (overall) header between the Arcade and New Posts links. There you will find a variety of specific examples with sample files, directions, real life scenarios, etc. Any help from there can be posted to the forum. I'd search there first. The new search method we have there (which we must thank Smozgur for that feature!) truly is awesome!

    As for your last request, could you give a little more information? What Application are you using this for? Try and be as detailed as possible. The more details you provide, the higher the level of service and precision will be given back.

  3. #3
    VBAX Regular
    Joined
    Sep 2004
    Posts
    8
    Location
    Quote Originally Posted by firefytr
    As for your last request, could you give a little more information? What Application are you using this for? Try and be as detailed as possible. The more details you provide, the higher the level of service and precision will be given back.
    Thank you in advance!!!!

    Here's my application looks like...

    1. Application Environment: Excel Spread Sheet [something looks like below]

    Pls refer to the attachment as I faced some prob. pasting it for your viewing purpose...sorry forthe inconveniences...

    2. What it does...
    New input line's sepcific cell info. (e.g. Equipment) will be checked with the rest of the existing lines (old lines before the new input) and to display whether there's any existing old data (all) clashes with the newly input data.

    Hope above info does give you a little picture of the application...

  4. #4
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Okay, moved to the Excel Help section. Your picture came through ok. You can also upload attachments here, when posting click Manage Attachments (ensure the file is zipped) press Browse, then Upload. Or if you have trouble just email it to me.

    So what exactly are you wanting to check there? Are you saying you want to see if the information on one line is the same as the line directly above it? If so, when do you want the check performed? What cells will be checked? What will be done if this condition is true? What if false?

  5. #5
    VBAX Regular
    Joined
    Sep 2004
    Posts
    8
    Location
    Quote Originally Posted by firefytr
    So what exactly are you wanting to check there? Are you saying you want to see if the information on one line is the same as the line directly above it?
    -Checking items are the data key in under Equipment and Recipe.
    -As and when there's a new line key-in, it will check through the whole ledger, which is all the lines before the new one.

    Quote Originally Posted by firefytr
    If so, when do you want the check performed? What cells will be checked?
    -There will be a button which the user is able to hit it and perform the "clash check".
    -Main cells to be checked are data under "Equipment" and "Recipe"

    Quote Originally Posted by firefytr
    What will be done if this condition is true? What if false?
    -If it is TRUE, it will generate a simple report stating all the clashed info which will display additional info. like "Issue No.", "Creation Date" and "Deletion Date" for reference.
    -If it is FALSE, it will pop up a dialogue box to tell the user that the new Recipe is Free.

    I'll be logging off soon, time to sleep...will check with you guys tom...

    cheers..

  6. #6
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Okay. Great explanation! Really, grade A. (some are not!) One more question. What format do you want your list? Do you want it only in a popup box? Do you want it on a printed sheet? Do you want it retained on/in a specific location in that book?

  7. #7
    VBAX Regular
    Joined
    Sep 2004
    Posts
    8
    Location
    Quote Originally Posted by firefytr
    Okay. Great explanation! Really, grade A. (some are not!)
    Thanks! :rofl

    Quote Originally Posted by firefytr
    One more question. What format do you want your list? Do you want it only in a popup box? Do you want it on a printed sheet? Do you want it retained on/in a specific location in that book?
    Output of is not really important, most important is to alert the user that certain new data input has some clashes with old/existing data. A simple prompt in the format of dalogue box or new excel sheet stored in a specific folder will do...

    Again, many thanks to you for all the helps...

  8. #8
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Maybe you could try this ...

    Option Explicit
    Sub clashCheck()
        Dim cel As Range, rng As Range, concV As String, lastR As Long, _
            ckRng As Range, msg As String
        lastR = Range("A65536").End(xlUp).Row
        Columns(1).Insert
        Range("A5:A" & lastR).Formula = "=D5&CHAR(224)&E5"
        Set ckRng = Range("A5:A" & lastR)
        Set rng = Range("D5:D" & lastR)
        For Each cel In rng
            concV = cel.Value & Chr(224) & cel.Offset(, 1).Value
            If WorksheetFunction.CountIf(ckRng, concV) > 1 Then
                msg = msg & "Equipment: " & cel.Value & ", on row " & cel.Row & " has a clash." & vbCr
            End If
    skipMe:
        Next cel
        Columns(1).Delete
        If msg <> "" Then
            MsgBox msg
            Exit Sub
        End If
        MsgBox "No clashes were found!", vbOKOnly + vbInformation, "No Clashes!"
    End Sub
    Not sure if it's exactly what you're looking for. Post back any additional needed details. Example workbook attached. HTH

Posting Permissions

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