Hi All,
I had a macro decide to stop working today, so I've spent the day rewriting it and am in the testing phase. For the life of me (probably because I've been staring at it all day), I can't figure out why my macro is hanging at the following point in underlined red (see code pasted below). The goal of this macro is to compare a text file that I receive from my sales broker each morning with customer account sales in it and compare to my customer name spreadsheet BEFORE I import it into my accounting software, then identify those customer names that don't match my customer names in my accounting software. My accounting software is the master file for my customer name spreadsheet and is updated monthly. The macro is embedded in the customer name spreadsheet. A fresh set of eyes to look at this macro and tell me what I'm missing would be welcome because I can't get this blinkety-blink-blink thing to work.
Thanks
Karen
Sub NotFindCustomer()
'
' NotFindCustomer Macro
' This Macro compares the ABC.xls spreadsheet with the QB Customer MACRO.xls file and highlights the no match, winery direct, samples, and credit memos.
'
' Keyboard Shortcut: Ctrl+Shift+T
'
Dim vDataOne, vDataTwo, vNotFound, vDataThree
Dim j As Long
Dim k As Long
Dim m As Long
Dim sSearch As String
Dim sData As String
Dim itype As String
'compare HWG sheet to QB customer list and flag any without match
With Sheets(1).Select
vDataOne = .Range("f1:f" & .Range("f65536").End(x1Up).Row) ' error on this line
End With
With Workbooks("QB Customer MACRO.xls").Sheets(QB)
vDataTwo = .Range("b5:b" & .Range("b65536").End(x1Up).Row)
End With
vNotFound = vDataOne
For j = 1 To UBound(vDataOne, 1)
sSearch = vDataOne(j, 1)
For k = 1 To UBound(vDataTwo, 1)
sData = vDataTwo(k, 1)
If InStr(sData, sSearch) Then
vNotFound(j, 1) = " "
End If
Next k
Next j
Sheets.Add
ActiveSheet.Name -"no match"
With Sheets("no match")
.Activate
.Range(Cells(1, 1), Cells(UBound(vNotFound, 1), 1)).Select
Selection = vNotFound
Selection.Sort .Range("a1")
.Range("a1").Select
End With
'search column "d" and if sample do yellow or if credit memo do green
Sheets(2).Activate
With Sheets(2)
vDataThree = .Range("D1:D" & .Range("d65536").End(x1Up).Row)
End With
For m = 1 To UBound(vDataThree, 1)
If itype = "SAMPLE" Then
Rows(m).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = x1Solid
End With
End If
If ittype = "SOBS" Then
Rows(m).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = x1Solid
End With
End If
If itype = "Inv Cr" Then
Rows(m).Select
With Selection.Interior
.ColorIndex = 4
.Pattern = x1Solid
End With
End If
If itype = "Inv Credit" Then
Rows(m).Select
With Selection.Interior
.ColorIndex = 4
.Pattern = x1Solid
End With
End If
If itype = "Price Crd" Then
Rows(m).Select
With Selection.Interior
.ColorIndex = 4
.Pattern = x1Solid
End With
End If
Next m
'search column "n", wintery direct=red
Sheets(2).Activate
With Sheets(2)
vDataFour = .Range("n1:n" & .Range("n65536").End(x1Up).Row)
End With
For m = 1 To UBound(vDataFour, 1)
itype = vDataFour(m, 1)
If itype = "WINERY DIRECT" Then
Rows(m).Select
With Selection.Interior
.ColorIndex = 3
.Pattern = x1Solid
End With
End If
Next m
'search wine code for reserve and abacus,
End Sub
Display More