VBA Script Resources

Dear all,

:bink: 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? :help

Cheers...

Hi 7 :006: 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. :yes

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. :yes

---

Thank you in advance!!!! :vv

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... :ipray:

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? :)

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..

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?

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...

Maybe you could try this ...

Code:

---

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