Solved Formatting Excel from Access (1 Viewer)

[tmyers]

Not sure if this should be in the Excel subforum or the modules as it is being done from Access but dealing with Excel.

I can't seem to word my question right for Google to provide me direction, so I apologize if this is a really simple one. Below is a snip of an example file:

I am looping through the columns looking for the value "Line Note:", which is simple enough, however what I cant figure out is once I find it, find the next value above it, cut and paste the note with an offset of 1 to the right so it ends up on the same line as the part number but one column over.

The finding of the note and the final cut/paste and offset I get, I just cant figure out how to dynamically find data above each line note as the amount of rows above it can change.

 

[Minty]

Use the row number.
Have a second variable that takes the current "found" row number and subtracts 1 and check the cell contents, rinse and repeat until you hit text or another value of "Line Note" and stop because you've missed it or there isn't one.

 

[tmyers]

Now why didn't I think of that as that is simple.
This what I get for trying to code first thing in the morning. Thank you Minty!

 

[Minty]

I've done loads of "data scraping" from thousands of Excel unformatted or randomly formatted text lines, and you get pretty inventive after a while, but often the simple answers are the best.

 

[tmyers]

Yeah I have learned that you do tend to get creative to make Excel files work with your table structure in Access. Sometimes Excel files dont have a lot of rhyme or reason to their formatting.

 

[tmyers]

Minty, for some reason my condition is never met for line 4 and I can't seem to figure out why. Did I mess up the syntax on it?

        With ws.Range("C:C")
            LastR = .Cells.Find("*", ws.Cells(1, 3), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
            For i = LastR To 2 Step -1
                If InStr(1, (.Range("C" & i).Value), "Line Note:") <> 0 Then
                    For c = i To 2 Step -1
                        If IsEmpty("E" & c) Then
                        'do nothing
                        Else
                            .Range("D" & i).Copy Destination:=.Range("E" & c)
                            Exit For
                        End If
                    Next c
                End If
            Next i
        End With

 

[Minty]

Chuck a breakpoint on the With ws.Range code line, make sure the sheet is visible and step through your code using F8
I suspect you're not getting to the rows you think you are.

 

[tmyers]

That was the problem. I expanded the range to not be just a single column, but from A to C and now it is going into the second for loop on the correct lines but now the copy/paste isnt doing anything . Ill just fix that after lunch.

Edit:
Fixed it. This works exactly how I wanted it to.

        With ws.Range("A:F")
            LastR = .Cells.Find("*", ws.Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
            For i = LastR To 2 Step -1
                If InStr(1, (ws.Range("C" & i).Value), "Line Note:") <> 0 Then
                    For c = i - 1 To 2 Step -1
                        If .Range("C" & c).Value = vbNullString Then
                        'do nothing but check to see if another note is hit first then exit is true
                            If .Range("C" & c).Value = "Line Note:" Then
                                Exit For
                            End If
                        Else
                            .Range("D" & i).Cut .Range("E" & c)
                            Exit For
                        End If
                    Next c
                End If
            Next i
        End With

 

[Pat Hartman]

Thanks for posting the solution. This is a very common problem when appending data from Excel.

One thing I would test before committing to it is what happens if you insert a row in the spreadsheet. Say you insert a row after 15 to add a second line of description, is the row order what you see or does the inserted row end up at the end of the recordset when you process it this way even though it looks like it was inserted as row 16. I haven't done a lot of work automating Excel but I have noticed that using TransferText I might get the rows in a different order than the way I see them when I open the sheet.

 

[tmyers]

Im having another slight issue with this same module. I am working on a different file now and trying to find if a string beings with an asterisk, but I cant seem to get my if statement to ever return true.

                If Left(.Range("D" & i), 1) = "*" Then
                    sNote = .Range("D" & i).Value
                    For c = i To i + 8 Step 1
                        If .Range("D" & c).Value = vbNullString Or Left(.Range("D" & c), 1) = "*" Then
                            .Range("E" & i).Value = sNote
                            .Range("D" & i).ClearContents
                            Exit For
                        Else
                            sNote = sNote & .Range("D" & c).Value
                            .Range("D" & c).ClearContents
                        End If
                    Next c
                End If

I have a feeling its because I am not encapsulating the asterisk right, but have tried "~*", "[*]" and "*[*]" yet the statement is never true.

Edit:
Looking at it again, I believe I messed up the If statement altogether. Going to redo it real quick.

Edit 2:
Redid the if statement and it worked. Sorry for the pointless post and I edited the code above to reflect the correct code.