calculation by vba

[agog12]

vba is placed in a seperate file c.xlsm
both files are located in a same place
there is a file name sample1.xlsx
open sample1.xlsx
in column N we have to use the formula =H2/M2 and paste the result in values in column N of sample1.xlsx
and in coulmn Q we have to use the formula =N2*P2 and paste the result in values in column Q of sample1.xlsx
and save and close the sample1.xlsx


note we have to use the formula till the end of the data (till the column H has data )
example if column H has data till H17 then we have to use the formula till
N17 & Q17

so plz have a look sir and help me in solving this problem by vba macro sir

 

[MARK858]

Then delete all those #N/A from A65537 to H1048576. No wonder that the code from DanteAmor couldn't find the last row that you expected.
Once you have done that the code DanteAmor posted will work as per your original request.

 

[agog12]

bro i done that also but again it is doing the same thing

 

[MARK858]

Did you clear the contents in columns N & Q first?
I just cleared all the #N/A columns N & Q.
Ran the code below, which is just the code by Dante amended to run from the 123 workbook and it runs correctly.

Sub calculation()
    Dim wb As Workbook, sh As Worksheet, lr As Long
    Set wb = ActiveWorkbook   'Workbooks.Open(ThisWorkbook.Path & "\sample1.xlsx")
    Set sh = wb.Sheets(1)
    lr = sh.Range("H" & Rows.Count).End(3).Row
    With sh.Range("N2:N" & lr)
        .Formula = "=H2/M2"
        .Value = .Value
    End With
    With sh.Range("Q2:Q" & lr)
        .Formula = "=N2*P2"
        .Value = .Value
    End With
    'wb.Close True
End Sub

I.e. it gives me the result below

123 (1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ASHOKLEY	EQ	73	73.95	69.5	69.9	69.9					100	0.699		1	0.699
3	NSE	BANKBARODA	EQ	76.4	77.6	73.3	76.3	76.3					100	0.763		1	0.763
4	NSE	BEL	EQ	76.55	76.55	72.85	73.95	73.95					100	0.7395		1	0.7395
5	NSE	FEDERALBNK	EQ	85.95	87.15	85.1	85.9	85.9					100	0.859		1	0.859
6	NSE	IDFCFIRSTB	EQ	37.95	37.95	36.6	36.8	36.8					100	0.368		1	0.368
7	NSE	ONGC	EQ	90.3	92.7	89.5	91.95	91.95					100	0.9195		1	0.9195
8	NSE	TATAPOWER	EQ	47.7	48.55	46.05	46.7	46.7					100	0.467		1	0.467
9	NSE	APOLLOTYRE	EQ	146	147	141	142.8	142.8					100	1.428		1	1.428
10	NSE	CANBK	EQ	150	150.7	140.15	143.7	143.7					100	1.437		1	1.437
11	NSE	COALINDIA	EQ	171.55	171.8	166.75	168.4	168.4					100	1.684		1	1.684
12	NSE	DLF	EQ	203.5	204.9	193.15	201.7	201.7					100	2.017		1	2.017
13	NSE	EQUITAS	EQ	111.5	112.55	107.05	107.85	107.85					100	1.0785		1	1.0785
14	NSE	GAIL	EQ	102	108.5	102	104.4	104.4					100	1.044		1	1.044
15	NSE	HINDALCO	EQ	162	162.2	154.1	155.9	155.9					100	1.559		1	1.559
16
17
18
Sheet1

 

[agog12]

Still i met with same result no problem now i have a solution for this

 

[agog12]

Thnx for ur great support bro

 

[agog12]

I GOT THE ISSUE POINT WHERE THE PROBLEM OCCURED IT WAS ANOTHER MACRO AND BCOZ OF THAT I WAS METING WITH THAT PROBLEM SIR THNX ALOT SIR

 

[MARK858]

Happy that you got it sorted.

 

[agog12]

yes bro from yesterday i am finding the error point now i got it coy and paste
this macro was wrong

 

[MARK858]

I don't think the macro in the other post is wrong for what you requested. It copies the entire columns as you asked for.
The issue is you have something (well actually a formula) giving the N/A result in those cells.
In the other thread post what is in one of those cells in workbook a, i.e. if it is a formula post the actual formula and someone will have a look at it.

 

[agog12]

bro coy and paste that file is a csv file and from that we have to copy and paste it to another file i ran the code step by step and that was causing an issue