CODEWITHSUNDEEP
vbaexcel
Coding Novice
Coding Novice

Reputation: 447

Declaring Variables in VBA to analyze Strings (But offset to see numbers!)

In the following code I have declared c to be used, but it seems as though it only works with numbers. I am looking for a string to compare with another string in another table on the same sheet.

The catch that makes this tricky is that I would like to offset the value to the cell adjacent to the left, and then compare one date with another. To make it more clear, my data is formatted like this: (Call it SetA)

         A        B         C         D    E  F      G          H          I
CreateTime  LotID  AnalysisSet  Slot#  X  Y  ResultType  TermName  ResultName

A few more columns to the right.... Lets call this SetB

     Y        Z        AA       AB    AC  AD     AE          AF        AG
CreateTime  LotID  AnalysisSet  Slot#  X  Y  ResultType  TermName  ResultName

Here is the code: 

Sub AIM36DBOCompare()
Dim n As Integer
n = 2
Dim PasteCount As Integer
Dim test1 As Long
Dim test2 As Long

PasteCount = 41
    Range("AD2:AD1000").Copy Destination:=Range("S41" & Lastrow)
    Do While n <= 1000
        If Cells(26, n) <> 0 Then
'--------------------------------------------
            With Worksheets(1).Range("b2:b10000")
                Set c = .Find(Cells(n, 26).String, LookIn:=xlValues)
                If Not c Is Nothing Then
                    Do
                        test1 = Cells(n, 25).Value
                        test2 = c.Offset(0, -1)
                        If Abs(Cells(n, 25) - c.Offset(0, -1)) <= 100 Then
                            c.Offset(0, -1).Copy
                            Cells(PasteCount, 17).Select
                            Selection.Paste
                            PasteCount = PasteCount + 1
                            Exit Do
                        End If
                        Set c = .Find(Cells(n, 26).Value, LookIn:=xlValues)
                    Loop While Not c Is Nothing
                End If
            End With
'--------------------------------------------
        End If
        If Cells(11, n) = 0 Then
            Exit Do
        End If
    n = n + 1
    Loop
End Sub

So to be more clear here is the code: Find LotID in SetB, find the first (of multiple) corresponding LotID in SetA. Compare date in SetB to the date in SetA by offsetting the variable by one cell and then subtracting the two (using absolute value).

If it is under 100 days, then do stuff!

Its not doing stuff :(. The date in SetA is showing up as nothing. I think it is because of the variable being used. Please help!

The code fails at Test2 (and consequently) in the "if test" If Abs(Cells(n, 25) - c.Offset(-1, 0)) <= 100 Then

enter image description here

Upvotes: 0

Views: 203

Answers (1)

Jane
Jane

Reputation: 851

You mentioned that you want to take the cell to the left of c. In the code, you are getting the value from the cell above. To see this, try the code below:

Sub test()
    Dim add As String
    Dim c As Range
    Dim offsetRange As Range

    Set c = Range("B2")
    Set offsetRange = c.Offset(-1, 0)
    add = offsetRange.Address
    Debug.Print add
    Debug.Print offsetRange.Value
End Sub

Offset takes rows first, then columns, so you would want to swap to 0 and -1, as below:

Sub test2()
    Dim add As String
    Dim c As Range
    Dim offsetRange As Range

    Set c = Range("B2")
    Set offsetRange = c.Offset(0, -1)
    add = offsetRange.Address
    Debug.Print add
    Debug.Print offsetRange.Value
End Sub

Also, you are declaring and getting the values for the variables test1 and test2, but you are not using these values when doing the comparison.

Upvotes: 1

Related Questions