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]

Hi thanks, this is the easiest route to go ,
No there will be multiple transaction between A and B , but the only distinguishing criteria would be in column E I have inserted; a purchase by one company would be a sale in another company , so there should be a formula in column E to insert "Sales of goods" if there is a "Purchase of goods" in B and vice versa
Please help me to adapt , the file
Data2.xlsm - Google Drive

 

[StephenCrump]

In the examples you've posted so far, a sale from A to B is matched to a purchase by B from A even though the amounts don't match.

So if you have, say, three sales A to B, and three purchases B from A, how will you know which sale is related to which purchase? Even worse, what if you have three sales A to B and four purchases B from A?

What is the end goal here?

 

[rdev]

No there will only be one sale from A and reciprocally 1 purchase of B from A
There can be another relationship linking A and B . For example A owes B and reciprocally B will show that it owes to A
By inserting a column E , we'll need a formula if B2 is "Purchase of Goods" then E2 would be "Sales of goods"
If B2 show Owed to , E2 should show 'receivable from' ; we will need then integrate column E in the sumifs with criteria in column B

 

[StephenCrump]

So perhaps something like this:

F2: =SUMIFS($D$2:$D$10,$A$2:$A$10,C2,$C$2:$C$10,A2,$B$2:$B$10,VLOOKUP(B2,TableOfOpposites,2,))

Excel 2010
	A	B	C	D	E	F
1	Company	Transaction	Transaction Party	Amount		Counter Amount
2	Alpa	Purchase of goods	Silicon	135,000		270,000
3	Tony	Sales of goods	Cinbata	12,000		-
4	Alpa	Sales of goods	Naturo	167,000		167,000
5	Alpa	Sales of goods	Cinbata	78,000		80,000
6	Silicon	Sales of goods	Alpa	270,000		135,000
7	Alpa	Owed to	Naturo	777,000		888,000
8	Naturo	Purchase of goods	Alpa	167,000		167,000
9	Cinbata	Purchase of goods	Alpa	80,000		78,000
10	Naturo	Receivable from	Alpa	888,000		777,000
11
12					TableOfOpposites
13					Purchase of goods	Sales of goods
14					Owed to	Receivable from
15					Sales of goods	Purchase of goods
16					Receivable from	Owed to
mathew