PDA

View Full Version : Macro to check and send data to Array.



Endorphin
10-19-2008, 11:40 PM
:hi: Hi All
I have a sheet (attached) which will contain voltages and dates. I am trying to find how to get a Macro to check that cells F1:F5 have data in and send that appropriate information (F4:F5) to the next empty cell for that specific ID when the Input Data button is pressed.
This is only in the planning stage at the moment and if the layout need to be changed to accommodate it is not a problem.
Have thought about using UserForm, but still in the same position (have not a clue how to do it)
I am very new to VBA, Normally just record a Macro and then try and figure out how it works and adjust it to what I am after.
Neil

GTO
10-19-2008, 11:57 PM
Greetings Neil -

Thanks for including an example workbook; makes it much easier to follow.
Now finding where the specific array of ID/Position/Aspect all match is easily doable. What do you want it to do if no match is found?

Mark

Endorphin
10-20-2008, 12:01 AM
Hi GTO,

If one of the 5 cells is missing, then just a msgBox to say, Please make sure all required cells are completed or something along those lines.

Neil

GTO
10-20-2008, 12:54 AM
Howdy Neil, :hi:

Here you go. I attached the wb as well. A rather simplistic approach and you may get better suggestions; but I thought this would show you the direction to think in.

Have a great day, I'm out (late here),

Mark

Option Explicit
Sub FindMatchingArray()
Dim aServiceID(1 To 3)
Dim rngTVal As Range, _
intCol As Integer, _
bolFound As Boolean, _
bolSlotAvail As Boolean
'// Note that we changed the CodeName of "Lamp Input." This way we can simply //
'// simply refer to the sheet object. //
With shtLampInput
'// check against to required cells, if any blank, warn user and bail... //
If .Cells(1, 6) = "" _
Or .Cells(2, 6) = "" _
Or .Cells(3, 6) = "" _
Or .Cells(4, 6) = "" _
Or .Cells(5, 6) = "" Then
MsgBox "Try again, you are missing info", 0, ""
Exit Sub
End If
End With
'// Make a small one-dimensional array to hold the ID, Aspect, and position to //
'// check against. //
aServiceID(1) = shtLampInput.Cells(1, 6).Value
aServiceID(2) = shtLampInput.Cells(2, 6).Value
aServiceID(3) = shtLampInput.Cells(3, 6).Value

'// In this rudimentary example, I picked row 2000 to quit searching after; you //
'// can change as desired. //
For Each rngTVal In shtLampInput.Range("H2:H2000")
'// If all three vals match... //
If rngTVal.Value = aServiceID(1) _
And rngTVal.Offset(0, 1).Value = aServiceID(2) _
And rngTVal.Offset(0, 2).Value = aServiceID(3) Then
'//...set a flag, then... //
bolFound = True
For intCol = 11 To 49 Step 2
'// check for the first available slot (column) to put the new //
'// voltage and date in. //
If shtLampInput.Cells(rngTVal.Row, intCol).Value = "" Then
shtLampInput.Cells(rngTVal.Row, intCol).Value = _
shtLampInput.Cells(4, 6)
shtLampInput.Cells(rngTVal.Row, intCol).Offset(0, 1).Value = _
shtLampInput.Cells(5, 6)
bolSlotAvail = True
Exit For
End If
Next
Exit For
End If
Next

'// In case the user jacked up an ID number or such, or, there are no remaining //
'// date/voltage slots left, warn user. //
If Not bolFound _
Or Not bolSlotAvail Then
MsgBox "Either there is no ID/Postition/Aspect matching, or there was" & _
" no slot available.", 0, ""
End If
End Sub

Endorphin
10-20-2008, 12:55 AM
GTO,
Many Thanks and have a good sleep..