| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through cells and holding values
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping through cells and holding values
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping through cells and holding values
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping through cells and holding values
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 wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping through cells and holding values
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 "Danny" wrote: 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 wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Looping and deleting thru cells which values offset | Excel Programming | |||
| looping through an giving values to cells in vba | Excel Discussion (Misc queries) | |||
| Looping thru values in a Range of Cells | Excel Programming | |||
| Holding form control values within VBA | Excel Programming | |||
| #Holding values in different format# | Excel Programming | |||
![Danny[_12_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
