Hi Gurus,
I need to write a access vba function to pass the excel file name, sheet name, column letter as parameters and then multiply a minus sign number to all the cells in that column. How can I do that? I wrote a not working code. Would you please change it for me to make it work? Thank you very much!
Public Function multiplycolumn (ByVal sourcename As String, ByVal srcsheetname As String, ByVal columnltr As String, byval multiplevalue as long) As Boolean
Dim oXL As Excel.Application
Dim oSheet As Excel.Worksheet
Dim oWKSource As Excel.Workbook
Dim oBook As Excel.Workbook
Dim strSourceBook As String
Dim strSourceSheet1 As String
Dim strSourceSheet2 As String
Dim raSource As Excel.Range
dim coltr as string
dim mulval as long
strSourceBook = sourcename
'Get instance of Excel and open the workbooks
Set oXL = CreateObject("Excel.Application")
Set oWKSource = oXL.Workbooks.Open(strSourceBook, , ReadOnly = True)
Dim LR As Long, i As Long
LR = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).PasteSpecial Paste:=xlPasteValues
For i = 1 To LR
if isnumeric(cells( & columnltr, i)) then
Cells(& columnltr, i).Value = Cells(& columnltr, i).Value * & multiplevalue
end if
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True
'Finish
'Save workbook we modified
oXL.DisplayAlerts = False
For Each oBook In oXL.Workbooks
oBook.Close False
Next
oXL.DisplayAlerts = True
oXL.Quit
Set oXL = Nothing
Set oWKSource = Nothing
Set oBook = Nothing
End Function
I need to write a access vba function to pass the excel file name, sheet name, column letter as parameters and then multiply a minus sign number to all the cells in that column. How can I do that? I wrote a not working code. Would you please change it for me to make it work? Thank you very much!
Public Function multiplycolumn (ByVal sourcename As String, ByVal srcsheetname As String, ByVal columnltr As String, byval multiplevalue as long) As Boolean
Dim oXL As Excel.Application
Dim oSheet As Excel.Worksheet
Dim oWKSource As Excel.Workbook
Dim oBook As Excel.Workbook
Dim strSourceBook As String
Dim strSourceSheet1 As String
Dim strSourceSheet2 As String
Dim raSource As Excel.Range
dim coltr as string
dim mulval as long
strSourceBook = sourcename
'Get instance of Excel and open the workbooks
Set oXL = CreateObject("Excel.Application")
Set oWKSource = oXL.Workbooks.Open(strSourceBook, , ReadOnly = True)
Dim LR As Long, i As Long
LR = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).PasteSpecial Paste:=xlPasteValues
For i = 1 To LR
if isnumeric(cells( & columnltr, i)) then
Cells(& columnltr, i).Value = Cells(& columnltr, i).Value * & multiplevalue
end if
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True
'Finish
'Save workbook we modified
oXL.DisplayAlerts = False
For Each oBook In oXL.Workbooks
oBook.Close False
Next
oXL.DisplayAlerts = True
oXL.Quit
Set oXL = Nothing
Set oWKSource = Nothing
Set oBook = Nothing
End Function