Missing cells

[DaSo]

Hi!

I´d like to ask you for help. Is there a way how to solve this problem:
I have Excel files whose 1st column always contains these values (not
talking about cell adresses) in the same order:A1, A2, A3...A12, B1,
B2...B12, C1, C2...C12.
Sometimes, however, some value is missing. Let´s assume that there is no
A4 and no B6 and no C7. How should I create a macro that controls if any
of these values are missing, and if so, it inserts a new row with this
missing value.

Thanks a lot,
DaSo



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Bob Phillips]

Alternative suggestion, rather than see what is missing, on workbook open,
always add them

Private Sub Workbook_Open()
Dim sValues As String
Dim aryValues
Dim i As Long

sValues =
"A1,A2,A3,A4,A5,A6,A7,A8,A9,A0,A11,A12,B1,B2,B3,B4,B5,B6,B7,B8,B9,B10,B11,B1
2, C1,C2,C3,C4,C5,C6,C7,C8,C9,C10,C11,C12"
aryValues = Split(sValues, ",")
With Worksheets("Sheet1")
.Columns("A:A").ClearContents
For i = 0 To UBound(aryValues)
.Cells(i + 1, "A").Value = aryValues(i)
Next i
End With

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[DaSo]

Hi Bob, thanks for your tip. However, I´ve forgotten to mention that
there are some values also in other colums, which means that after
running this macro, some of the values will no longer be in the correct
row (when for example I have A1, A2,missing A3, A4...A12; I than run
macro and all the information within the row A4 will be a part of A3).
So what I would need in this case is to add a new row after A2.
Any suggestion?

Thanks,Dan



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[gocush]

How about

Private Sub Workbook_Open()
Dim sValues As String
Dim aryValues
Dim i As Long

sValues = "A1,A2,A3,A4,A5,A6,A7,A8,A9,A0,A11,A12,B1,B2,B3,B4,B5,B6, " & _
"B7,B8,B9,B10,B11,B12, C1,C2,C3,C4,C5,C6,C7,C8,C9,C10,C11,C12"
aryValues = Split(sValues, ",")
With Worksheets("Sheet1")

For i = 0 To UBound(aryValues)
If .Cells(i + 1, 1).Text <> aryValues(i) Then
.Cells(i + 1, 1).EntireRow.Insert xlDown
.Cells(i + 1, 1).Value = aryValues(i)
End If
Next i

End With

End Sub

 

[DaSo]

Thanks a lot! Great!



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!