Looping through cells and holding values

[Danny]

Hi all
i have a spreadsheet with the following data....

Col A Col B Col C Col D Col E
ABC 123 A1 ABC A1&123
A2 ABC A2&123
A3 ABC A3&123
DEF 456 A1 DEF A1&456
A2 DEF A2&456
GHI 789 A1 GHI A1&789
A4 GHI A2&789


What I am trying to do is check Col A and B with Col C, D, E. The
latter is added manually the former automatically so always wnat to
check that in line 2 col D it matches with col A line 1 and col E
matches with col C and Col B.

My code so far to do this is below but having problems with the
looping and holding col A and B to do the check. Any help would be
appreciated. Thanks very much.


Sub datachecker()
Dim n, myLen As Integer
Dim myTemplate, mynode, myComp, myLinkType As String

Application.ScreenUpdating = False

With ThisWorkbook.Sheets(7)

Do Until Range("E2").Offset(n, 0).value = ""

If Range("A2").Offset(n, 0).value <> "" Then
mynode = Range("A2").Offset(n, 0).value

Do Until Range("A2").Offset(n + 1, 0).value
<> ""

If Range("A2").Offset(n, 0).value <>
Range("F2").Offset(n, 0).value Then
Range("F2").Offset(n, 0).value =
Range("A2").Offset(n, 0).value
End If

Set lngEndRow = Range("A2",
Range("A2").End(xlDown))

If Range("B2").Offset(n, 0).value <>
"" Then
myTemplate = Range("B2").Offset(n,
0).value
ElseIf VBA.Left(Range("A2").Offset(n,
0).value, 2) = "HP" Then
myTemplate = "HP"
End If
If Range("C2").Offset(n, 0).value <>
"" Then
myComp = Range("C2").Offset(n,
0).value
myLinkType = myTemplate & "_" &
myComp
myLen = VBA.Len(myLinkType)
End If

If VBA.Left(Range("G2").Offset(n,
0).value, myLen) <> myLinkType Then
MsgBox "Correct"
End If

n = n + 1
Loop

End If

n = n + 1
Loop

End With

Application.ScreenUpdating = True

End Sub

 

[Joel]

I don't undstand what you are trying to do. If you have two DO lops you need
two counters. also if you have a With statement you need a period in front
of you .Range statments. I made some changes to your code below that may get
you futher along.

Sub datachecker()
Dim n, myLen As Integer
Dim myTemplate, mynode, myComp, myLinkType As String

Application.ScreenUpdating = False

With ThisWorkbook.Sheets(7)

Do Until .Range("E" & RowCount).Value = ""

RowCount = 2
If .Range("A" & RowCount).Value <> "" Then
mynode = .Range("A" & RowCount).Value

RowCount2 = RowCount + 1
Do Until .Range("A" & (RowCount2)).Value <> ""

If .Range("A" & RowCount).Value <> _
.Range("F" & RowCount).Value Then

.Range("F" & RowCount).Value = _
.Range("A" & RowCount).Value
End If

Set lngEndRow = .Range("A2", Range("A2").End(xlDown))

If .Range("B" & RowCount).Value <> "" Then

myTemplate = .Range("B" & RowCount).Value

Else
If VBA.Left(.Range("A" & RowCount).Value, 2) = "HP" Then

myTemplate = "HP"
End If
End If

If .Range("C" & RowCount).Value <> "" Then

myComp = .Range("C" & RowCount).Value

myLinkType = myTemplate & "_" & myComp
myLen = VBA.Len(myLinkType)
End If

If VBA.Left(.Range("G" & RowCount).Value, myLen) <> _
myLinkType Then

MsgBox "Correct"
End If

RowCount2 = RowCount2 + 1
Loop

End If

RowCount = RowCount + 1
Loop

End With

Application.ScreenUpdating = True

End Sub

 

[Danny]

Hello
Thank you for the response, what I am trying to do is enusre that the
value in Col D is the same as Col A, it must always match. So as I
loop through each cell in Col A I want to check that what is in Col D
is the same.
This method also applies to Col E. I want to check that Col E is a
concatenation of Col C and Col B.
I guess this requires a loop which is what I have in the code but also
I would need to hold the value in line 1 of Col A so that I can use it
to compare Col D in line 1, 2, 3 etc. Unless there is a different
method to do this which does not require a loop.
Thanks again

 

[Joel]

You only need one DO loop

Sub datachecker()
Dim n, myLen As Integer
Dim myTemplate, mynode, myComp, myLinkType As String

Application.ScreenUpdating = False

With ThisWorkbook.Sheets(7)


RowCount = 2
Do Until .Range("E" & RowCount).Value = ""

Correct = true
If .Range("A" & RowCount).Value <> "" Then
mynode = .Range("A" & RowCount).Value


If .Range("A" & RowCount).Value <> _
.Range("D" & RowCount).Value Then

Correct = False
.Range("D" & RowCount).Value = _
.Range("A" & RowCount).Value
End If
end if

If .Range("B" & RowCount).Value <> "" Then


myTemplate = .Range("B" & RowCount).Value

If Left(.Range("E" & RowCount).Value, len(MyTemplate)) <>
MyTemplate Then

Correct = False
End If

myComp = .Range("C" & RowCount).Value

If mid(.Range("E" & RowCount).Value, len(MyTemplate)+2) <> myComp
Then

Correct = False
End If
End If

if Correct = True then
msgbox("Row" & RowCount & "is correct")
end if
RowCount = RowCount + 1
Loop

End With

Application.ScreenUpdating = True

End Sub

 

[Danny]

Hi again and thank you
Where the looping bit fails and what I am trying to achieve is; I need
to hold or store the value within line 1 of col A so I can perform the
check in Col D. And then when I get to Line 4 and Col A changes grab
this value and use it check the next rows in the spreadsheet in Col D.
This is what I cannot do at the moment is work out how to store/hold
the value in Col A until a new value is found.
Thanks

 

[Joel]

Sub datachecker()
Dim n, myLen As Integer
Dim myTemplate, mynode, myComp, myLinkType As String

Application.ScreenUpdating = False

With ThisWorkbook.Sheets(7)


RowCount = 2
Do Until .Range("E" & RowCount).Value = ""

Correct = True
If .Range("A" & RowCount).Value <> "" Then
mynode = .Range("A" & RowCount).Value
myTemplate = .Range("B" & RowCount).Value
End If

If mynode <> .Range("D" & RowCount).Value Then

Correct = False
.Range("D" & RowCount).Value = mynode
End If

CompareString = .Range("C" & RowCount).Value & _
"&" & myTemplate
If CompareString <> .Range("E" & RowCount).Value Then
Correct = False
End If

If Correct = True Then
MsgBox ("Row" & RowCount & "is correct")
End If
RowCount = RowCount + 1
Loop

End With