Looping through cells and holding values
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" wrote:
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 wrote:
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" wrote:
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
|