PDA

View Full Version : Attemping to loop data/extract/compare



pleasehelpme
05-29-2018, 07:05 AM
Hi there,

I apologize for aking such a detailed question on my first post.

I have been struggling with the following for about a week.

Essentially, I am trying to get it to identify specific cells in the cost code sheet, and use them as
set codes to compare to the other sheet. If the first four characters match in other sheet, but not the
whole code, I want it to replace the code with the code from the cost code sheet and then turn the cell pink.

If the whole string matches, I just want it to turn green, and if it doesn't match, I want it to turn purple.

Please see below.

Thank you.



Sub findString2()

Dim cell As Range
Dim result As String
Dim xRange As Long, yRange As Long
Dim POAFE As Range
Dim NonPOAFE As Range
Dim PartialAFE As String
Dim i As Integer
Dim a As Integer
Dim b As Integer
Dim n As Integer
Dim Result2 As String
Dim FullAFE As String
Dim Result1(1 To 2000)
Dim wb As Workbook
Dim ProjectPO As Worksheet
Dim CostReport As Worksheet
Dim NonPO As Worksheet

With ThisWorkbook

Set wb = ActiveWorkbook
Set CostReport = wb.Sheets("Cost Report")
Set ProjectPO = wb.Sheets("ProjPO")
Set NonPO = wb.Sheets("Non-PO")
CostReport
'Application.Workbooks("create-a-macro").Worksheets("Cost Report").Range("B" & Rows.Count).End(xlUp).Row = k
Dim k As Long
Dim z As Long
Dim x As Long
MsgBox ("Cost Report=" & CostReport.Name)
k = CostReport.Range("A" & Rows.Count).End(xlUp).Row
z = ProjectPO.Range("I" & Rows.Count).End(xlUp).Row
x = NonPO.Range("K" & Rows.Count).End(xlUp).Row

'For Each cell In costrange
'MsgBox (cell.Value & "nerd")
'Next
a = 0
For i = 1 To k
PartialAFE = Left(CostReport.Range("A" & i), 4)
If PartialAFE = "" Then
IsError ("")
FullAFE = CostReport.Range("A" & i)
If FullAFE = "" Then
IsError ("")
End If
End If
On Error Resume Next
Set POAFE = ProjectPO.Range("I1:I" & z)

For Each cell In POAFE
n = n + 1
If cell.Value Like "CC" & "*" Then
'MsgBox (cell.Value & "NOT SO DUMB")
'End If

'MsgBox (i)
If InStr(1, cell.Value, PartialAFE) > 0 Then
'And PartialAFE <> "" Then
b = b + 1
Result1(a + b) = n
If InStr(1, cell.Value, PartialAFE) > 0 And FullAFE <> cell.Value Then
'And PartialAFE <> "" And FullAFE <> "" Then
Cells(Result1(a + b), 9).Interior.ColorIndex = 38
result = result + cell.Address(False, False) + "-"
cell.Value = FullAFE
MsgBox (result & "confirmed and changed!")
Else
Cells(Result1(a + b), 9).Interior.ColorIndex = 43
n = n + 1
End If
End If
If InStr(1, cell.Value, PartialAFE) = 0 And i = 1 Then
cell.Interior.ColorIndex = 39
MsgBox "hey"
End If
If n = z Then Result1(a + b + 1) = -1
End If
Next cell
Next i
End With
End Sub

Hightree
05-29-2018, 09:00 AM
What value has n on start?

pleasehelpme
05-29-2018, 09:54 AM
i thought it defaulted to 0 or 1 but maybe i'm stupid?

Hightree
05-30-2018, 03:20 AM
i thought it defaulted to 0 or 1 but maybe i'm stupid?

No, I think you are ‘t stupid, but always When I see code I looking for the initialisation of the variable they can course much trouble When you don’t. Then you always must ad a value, so you know by debugging or the value change

snb
05-30-2018, 04:04 AM
Why don't you upload a sample file ?


Sub M_snb()
Dim n As Integer
MsgBox n
End Sub