Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Missing cells
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Missing cells
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" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Missing cells
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Missing cells
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" wrote: Hi Bob, thanks for your tip. However, I4ve 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Missing cells
Thanks a lot! Great!
*** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Right Click menu missing for cells | Setting up and Configuration of Excel | |||
Missing text in cells | Excel Discussion (Misc queries) | |||
Missing Cells | Excel Discussion (Misc queries) | |||
Missing cells, But they're NOT hidden | Excel Worksheet Functions | |||
assigning "missing value" to cells | Excel Discussion (Misc queries) |