Problem with macro

[Legacy 487876]

I have written the following macro
Sub Macro()
Dim LastBlankRow As Long
Dim Lrow As Single
LastBlankRow = Cells(Rows.Count, 12).End(xlUp).Row + 1

Cells(LastBlankRow, 12).Value = "1"

Workbooks("MASTER STOCK CARD.xlsm").Activate

Worksheets("Nail Cards").Range("C2:C4012").Find(Range("R7").Value).Offset(0, -1).Select

Lrow = Workbooks("MASTER STOCK CARD").Worksheets("Nail Cards").Range("B" & Rows.Count).End(xlUp).Row + 1

Workbooks("MASTER STOCK CARD").Worksheets("Nail Cards").Range("P" & Lrow & ":C" & Lrow) = Workbooks("MASTER STOCK CARD").Worksheets("Nail Cards").Range("P3:Q3").Value

End Sub

I am working on a set of stock control cards (68 individual cards) which have inputs in column “B” for the Quantity Sold and column “C” for the date sold. The quantity and dates are to be picked up from cells “P1” for the Quantity and “Q1” for the date.

The stock card to be selected for the input comes from cell “R7” This cell gives the item number (ie. The card number to have an entry made)

The existing macro below selects the right stock card numbered in range “R7” and places the cursor on the first entry row in column “B”

I now want to enter the quantity sold and the date in the next empty cell down.

HOWEVER no matter what I try I cannot get the cursor to move beyond Worksheets("Nail Cards").Range("C2:C4012").Find(Range("R7").Value).Offset(0, -1).Select.

The following last part of the macro is what I thought would input the data into the appropriate cells but does not appear to activate


Lrow = Worksheets("Nail Cards").Range("B" & Rows.Count).End(xlUp).Row + 1

Worksheets("Nail Cards").Range("B" & Lrow & ":C" & Lrow) = Worksheets("Nail Cards").Range("P1:Q1").Value

 

[DanteAmor]

Hi and welcome to MrExcel.

I'm a bit confused with your explanation and what you have in your code, some things don't match.
It would be best if you explained what the final objective is, help your explanation with an example of your sheet. Use the XL2BB tool minisheet to set your example.

 

[Legacy 487876]

Hi and welcome to MrExcel.

I'm a bit confused with your explanation and what you have in your code, some things don't match.
It would be best if you explained what the final objective is, help your explanation with an example of your sheet. Use the XL2BB tool minisheet to set your example.

 

[Legacy 487876]

MASTER STOCK CARD.xlsm
	A	B	C							J	K	L		N	O	P	Q	R
1	DESKTOP															2	15/01/2022
2
3	NAIL SUMMARY	ITEM	1
4		xxx								Unit sell	4.00
5		Annular Ringshank 2.65 x 40mm Stainless Steel								Re-order Qty	0
6		xxx								Stockist Y/N	YES
7		xxx								Pack Qty	0						ITEM	1
8		SALE	DATE							RE-ORDER	RE-ORDER	RE-ORDER		SUPPLIER
9		QTY								QTY	UNIT COST	DATE		COMMENTS			QTY	2
10										6
11																	DATE	15/01/2022
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
Nail Cards

Cell Formulas
Range	Formula
P1	P1	=R9
Q1	Q1	=R11
K4	K4	='NAIL SUMMARY'!F5
K5	K5	='NAIL SUMMARY'!J5
K6	K6	=IF(J10>=1,"YES","NO")
K7	K7	='NAIL SUMMARY'!G5
R7	R7	=IFERROR('[SALES 1.xlsm]SALES ENTRY'!W15,"")
R9	R9	=IFERROR('[SALES 1.xlsm]SALES ENTRY'!W26,"")
R11	R11	=IFERROR('[SALES 1.xlsm]SALES ENTRY'!$H$10,"")

This is the first time that I have used XL2BB so I hope that it comes to you ok. The final objective is to place the quantity and date of a transaction shown in cells P1 and Q1 into the next empty row in the SALE QTY and DATE columns.
I hope that this helps.

 

[DanteAmor]

This is the first time that I have used XL2BB so I hope that it comes to you ok.

The example is perfect!

Try this:

Sub Test()
  Dim sh As Worksheet
  Set sh = Workbooks("MASTER STOCK CARD.xlsm").Sheets("Nail Cards")
  sh.Range("B" & Rows.Count).End(3)(2).Resize(1, 2).Value = Array(sh.Range("P1").Value, sh.Range("Q1").Value)
End Sub

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Problem with writing VBA Macro
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Legacy 487876]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Problem with writing VBA Macro
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Thank you. Being new to your forum and its operation it is a bit of a learning curve. I shall try not to this in the future