Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2007
    Posts
    88

    Unanswered: access vba function to add a minus sign "-" to all the cells in entire single column

    I need to write a access vba function to pass the excel file name, sheet name, column letter as parameters and then add a minus sign "-" to all the cells in that column. The cells in the column contain positive numbers. I would like to add a "-" to all the numbers to make them negative numbers. How can I do that? Thank you very much!

  2. #2
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Multiply this positive numbers with -1.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    or run some Excel VBA to iterate through each cell int he column
    test if that value is positive if so multiply by -1

    or
    take the ABSolute value and multiply by -1
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Aug 2007
    Posts
    88
    Quote Originally Posted by MStef-ZG View Post
    Multiply this positive numbers with -1.
    Thanks for replying. Would you please share the VBA code with me? I am not good at it. Thanks.

  5. #5
    Join Date
    Aug 2007
    Posts
    88
    Quote Originally Posted by healdem View Post
    or run some Excel VBA to iterate through each cell int he column
    test if that value is positive if so multiply by -1

    or
    take the ABSolute value and multiply by -1
    Thanks for replying. Would you please show the VBA code to me? I am not good at it. How to iterate through ? How to take Absolute for cells from Access VBA?

    Thanks.

  6. #6
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Send a short example of your MDB, (access 2000 or 2002-2003).

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    how is your data gettign into the Excel spreadsheet?
    where ever the source of the data lies, thats where you need to fix this
    you can access the Excel spreadsheet directly from Access, see the code bank for examples (IIRC they were done by PootleFlump. however it can be a pig to do


    the outline tof the Excel VBA will be soemthign like

    identify the cells affected.
    apply any correction
    at the end of the list
    go back and select all cells
    copy and paste special and paste back the corrected values

    so you need to bone up on Excel....

    writign Excel VBA isn't that difficult if you record a macro, then examine the macro you very quickly get an idea of how to apply that to your VBA code

    personally I don't have the time or inclination to delve into Excel VBA, I've more important (read fee paying) things to work on that your problem. make an effort first and then come back if needed to refine the process. when doing so it will make things easier if you can supply an Excel Spreadsheet and Access MDB
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Aug 2007
    Posts
    88
    Quote Originally Posted by healdem View Post
    how is your data gettign into the Excel spreadsheet?
    where ever the source of the data lies, thats where you need to fix this
    you can access the Excel spreadsheet directly from Access, see the code bank for examples (IIRC they were done by PootleFlump. however it can be a pig to do


    the outline tof the Excel VBA will be soemthign like

    identify the cells affected.
    apply any correction
    at the end of the list
    go back and select all cells
    copy and paste special and paste back the corrected values

    so you need to bone up on Excel....

    writign Excel VBA isn't that difficult if you record a macro, then examine the macro you very quickly get an idea of how to apply that to your VBA code

    personally I don't have the time or inclination to delve into Excel VBA, I've more important (read fee paying) things to work on that your problem. make an effort first and then come back if needed to refine the process. when doing so it will make things easier if you can supply an Excel Spreadsheet and Access MDB
    Sorry for taking so long to reply. I have no experience in Excel and I don't know how to record macro. I only know Access with limited knowledge. I tried to research online these couple of days and draft a none working code. Would you please help me out? Thank you very much!

    Public Function PasteTranspose(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)



    Set raSource = oWKSource.Worksheets(srcsheetname).Range(sourceran gea & ":" & sourcerangeb)




    raSource.Copy

    raDestination.PasteSpecial xlPasteValues, Transpose:=True 'very important..Excel buildin function paste by value

    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

  9. #9
    Join Date
    Aug 2007
    Posts
    88
    Quote Originally Posted by MStef-ZG View Post
    Send a short example of your MDB, (access 2000 or 2002-2003).
    Sorry for not being able to respond earlier. I am using Access 2000. I don't know about Excel. What do you mean by a short example of my MDB? Do you mean the unfinished (not working) vba code?

    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •