PDA

View Full Version : Sleeper: VBA Script Resources



-7-
09-14-2004, 08:08 AM
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...

Zack Barresse
09-14-2004, 08:39 AM
Hi 7 :006: Welcome to VBAX!!

We have an excellent resource library here at VBAX we like to call our Knowledge Base (http://www.vbaexpress.com/kb). 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

-7-
09-14-2004, 08:58 AM
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. :yesThank 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:

Zack Barresse
09-14-2004, 09:39 AM
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 (firefytr@vbaexpress.com) 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? :)

-7-
09-14-2004, 10:00 AM
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.


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"


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

Zack Barresse
09-14-2004, 10:06 AM
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-
09-14-2004, 10:13 AM
Okay. Great explanation! Really, grade A. (some are not!) Thanks! :rofl


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

Zack Barresse
09-14-2004, 10:43 AM
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