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]

Put the first code below in a regular module in the sample1 workbook and run it.
Then put the second code in a regular module in the sample1 workbook, run it and let me know what number it gives you.

Sub Trimit()
    Dim myCell As Range, myRng As Range
    
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .calculation = xlCalculationManual
    End With

    Set myRng = Sheets(1).Columns(8)
    
    With myRng
        .Replace What:=Chr(160), Replacement:=Chr(32), LookAt:=xlPart
        .Replace What:=Chr(13) & Chr(10), Replacement:=Chr(32), LookAt:=xlPart
        .Replace What:=Chr(13), Replacement:=Chr(32), LookAt:=xlPart
        .Replace What:=Chr(21), Replacement:=Chr(32), LookAt:=xlPart
        .Replace What:=Chr(8), Replacement:=Chr(32), LookAt:=xlPart
        .Replace What:=Chr(9), Replacement:=Chr(32), LookAt:=xlPart
    End With

    On Error Resume Next
    For Each myCell In Intersect(myRng, _
                                 myRng.SpecialCells(xlConstants, xlTextValues))
        myCell.Value = Application.Trim(myCell.Value)
    Next myCell
    On Error GoTo 0

    With Application
        .calculation = xlCalculationAutomatic
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
End Sub

Sub test1x()
Dim lr As Long
lr = Sheets(1).Columns("H").Find("*", , xlValues, , xlByRows, xlPrevious).Row
MsgBox lr
End Sub

 

[agog12]

bro u worked hard i know bro but can u look at the vba code which i ran
is it perfect?
if u think its perfect then i am runing this code
bro i think that vba code will be incorrect kidly relook at it

 

[MARK858]

Run the code I posted.... there is nothing wrong with the code DanteAmor posted, it is the data in sample1.

 

[agog12]

1048576 i am getting bro

 

[MARK858]

Can you upload the sample1 workbook to a free file sharing site, either www.box.com or www.dropbox.com.
Mark the file for sharing and paste the url it creates in the thread.
Make sure that you alter any sensitive data before uploading.

 

[agog12]

SEE THE FILE

 

[MARK858]

No an image is no good, I need the actual file, please see the instructions in post 35.

 

[agog12]

123.xlsb

Shared with Dropbox

www.dropbox.com

 

[MARK858]

Is that the sample1 workbook?

 

[agog12]

yes i renamed it