lastrow value is nil

[rdev]

Please advise why the variable 'lastrow' is storing a value of 0 when there are data up to row 100 in column A

Sub usingFind()
Dim ms As Worksheet
Dim lastrow As Long, i As Long, c
Dim myCell As Range
Set ms = Sheets("mathew")
lastrow = ms.Range("A" & Rows.Count).End(xlUp).Row
x = 2
y = 3

c = ms.Cells(x, y).Value
u = ms.Cells(x, y).Offset(0, -1).Value
If u = "Purchase of goods" Then u1 = "Sales of goods"
If u = "Sales of goods" Then u1 = "Purchase of goods"
Set myCell = ms.Range("A1:A" & 1000).Find(What:=c, LookIn:=xlValues)

If Not myCell Is Nothing And myCell.Offset(0, 1).Value = u1 And _
myCell.Offset(0, 2).Value = c Then
Cells(x, 5).Value = c
Cells(x, 5).Value = myCell.Offset(0, 3).Value
myCell.Offset(0, 4).Value = Cells(x, 1).Value
myCell.Offset(0, 4).Value = Cells(x, 1).Value

End If
Exit Sub
End Sub

 

[rdev]

Hello, the above is fixed
I want to test 3 conditions for the code to proceed and return a value . Two AND is not working as per below. What could be the way round
If Not myCell Is Nothing And myCell.Offset(0, 1).Value = u1 And _
myCell.Offset(0, 2).Value = c Then
Cells(x, 5).Value = c
Cells(x, 5).Value = myCell.Offset(0, 3).Value
myCell.Offset(0, 4).Value = Cells(x, 1).Value
myCell.Offset(0, 4).Value = Cells(x, 1).Value
End If

 

[MrTeeny]

try amending

lastrow = ms.Range("A" & Rows.Count).End(xlUp).Row

to



lastrow = ms.Cells(Rows.Count, "A").End(xlUp).Row

 

[StephenCrump]

Two AND is not working as per below.

What do you mean by "not working". Not doing what you want it to do? Or producing an error?

If the .Find doesn't find a value, then myCell will be Nothing, and you will get an error message here when you try using mycell.Offset:

If Not myCell Is Nothing And myCell.Offset(0, 1).Value = u1 And _
    myCell.Offset(0, 2).Value = c Then

Do it in two steps

If Not myCell Is Nothing Then
    If myCell.Offset(0, 1).Value = u1 And myCell.Offset(0, 2).Value = c Then

 

[rdev]

What do you mean by "not working". Not doing what you want it to do? Or producing an error?

If the .Find doesn't find a value, then myCell will be Nothing, and you will get an error message here when you try using mycell.Offset:

If Not myCell Is Nothing And myCell.Offset(0, 1).Value = u1 And _
    myCell.Offset(0, 2).Value = c Then

Do it in two steps

If Not myCell Is Nothing Then
    If myCell.Offset(0, 1).Value = u1 And myCell.Offset(0, 2).Value = c Then

Please help me with the code . The file link is shared, the code is not getting through the 3rd condition . The code should go to the last row of data in column A . If a value of c is not found , then return in column E and F .. 'no match'

Sub usingFind()
Dim ms As Worksheet
Dim lastrow As Long, i As Long
Dim myCell As Range
Set ms = Sheets("mathew")
lastrow = Range("A" & Rows.Count).End(xlUp).Row
x = 2
y = 3
For x = 2 To 5 Step 1
    With ms.Range("A1:A" & lastrow)
        c = ms.Cells(x, y).Value
        u = ms.Cells(x, y).Offset(0, -1).Value
        If u = "Purchase of goods" Then u1 = "Sales of goods"
        If u = "Sales of goods" Then u1 = "Purchase of goods"
        Set myCell = .Find(c, LookIn:=xlValues, lookat:=xlWhole)
        If Not myCell Is Nothing Then
        If myCell.Offset(0, 1).Value = u1 Then
           If myCell.Offset(0, 2).Value = c Then
                Cells(x, 5).Value = myCell.Value
                Cells(x, 6).Value = myCell.Offset(0, 3).Value
                myCell.Offset(0, 4).Value = Cells(x, 1).Value
                myCell.Offset(0, 5).Value = Cells(x, 4).Value
            End If
         End If
        End If
    End With
    u = ""
    u1 = ""
    
Next x

Data2.xlsm - Google Drive

 

[rdev]

Data2.xlsm - Google Drive
The file link

 

[StephenCrump]

You can probably do this with some simple formulae, rather than VBA.

But at the moment, I'm not clear what you're tyying to do?

For the data illustrated, what results do you want to see, and why?

Excel 2010
	A	B	C	D	E	F	G
1	Company	Transaction	Transaction Party	Amount	Match Company	Counter Amount
2	Alpa	Purchase of goods	Silicon	135,000
3	Tony	Sales of goods	Cinbata	12,000
4	Alpa	Sales of goods	Naturo	167,000
5	Alpa	Sales of goods	Cinbata	78,000
6	Silicon	Sales of goods	Alpa	270,000
7	Naturo	Purchase of goods	Alpa	167,000
8	Cinbata	Purchase of goods	Alpa	80,000
mathew

 

[rdev]

Please see attached the result
Data2.xlsm - Google Drive

I am expecting some 26000 lines to execute , the macro run will help

 

[rdev]

Please see attached the result
Data2.xlsm - Google Drive

I am expecting some 26000 lines to execute , the macro run will help

 

[StephenCrump]

A different approach:

Dim lFirstRow As Long, lLastRow As Long

lFirstRow = 2
lLastRow = Range("A" & Rows.Count).End(xlUp).Row
With Range("E" & lFirstRow & ":F" & lLastRow)
    .Columns(1).Formula = "=IF(F" & lFirstRow & "=0,""No result"",C" & lFirstRow & ")"
    .Columns(2).Formula = "=SUMIFS(D$" & lFirstRow & ":D$" & lLastRow & ",A$" & lFirstRow & ":A$" & lLastRow & ",C2,C$" & lFirstRow & ":C$" & lLastRow & ",A2)"
    .Value = .Value
End With

Excel 2010
	A	B	C	D	E	F
1	Company	Transaction	Transaction Party	Amount	Match Company	Counter Amount
2	Alpa	Purchase of goods	Silicon	135,000	Silicon	270,000
3	Tony	Sales of goods	Cinbata	12,000	No result	-
4	Alpa	Sales of goods	Naturo	167,000	Naturo	167,000
5	Alpa	Sales of goods	Cinbata	78,000	Cinbata	80,000
6	Silicon	Sales of goods	Alpa	270,000	Alpa	135,000
7	Naturo	Purchase of goods	Alpa	167,000	Alpa	167,000
8	Cinbata	Purchase of goods	Alpa	80,000	Alpa	78,000
mathew

But will there always only one set of transactions for any pair of companies, i.e.

A --> B, and
B --> A

i.e. rather than multiple transactions between A and B?