+ Reply to Thread
Results 1 to 5 of 5

macro to copy cell contents then remove substring of contents in new cell - Help

  1. #1
    Registered User
    Join Date
    01-27-2011
    Location
    Pittsburgh
    MS-Off Ver
    Excel 2007
    Posts
    4

    macro to copy cell contents then remove substring of contents in new cell - Help

    I have a range of cells, e.g. g1-g1000 that contains two type of entries: xx.xx or xx.xxCR, where xx.xx are numbers.

    I want to search the range of cells for xx.xxCR and when a cell is found, copy the contents to the adjacent cell minus the CR, then delete the value in the original cell.

    Cells that contain xx.xx will not haven any thing done to them.

    E.G. cell g5 contains 23.67CR; after running the algorithm, cell h5 contains 23.67 and g5 is empty.

    Do this for the range of values in g0-g1000

    Here is my attempt:
    Dim i
    For i = 1 To 30
    If InStr(UCase(Cells(i, "G")), "CR") Then
    MsgBox "The string 'CR' was found in cell " & Cells(i, "G").Address(0, 0)
    ' Copy the cell containing xx.xxCR to the adjacent cell
    Range(Cells(i, "G")).Select ' Keep getting error 1004 at this line
    Range(Cells(i, "G")).Copy
    Range(Cells(i, "H")).Select
    ActiveSheet.Paste
    ' Remove the CR from the adjacent cell e.g. "C", just leaving xx.xx
    Cells(i, "H") = WorksheetFunction.Substitute(Cells(i, "H"), "CR", "")
    'Now Remove the contents of the cell where CR was found - how do this?

    End If
    Next

    I keep getting runtime error 1004 Application defined or object defined error at the line:Range(Cells(i, "G")).Select

    Can someone spot the error of my ways?
    Thank you!
    -J

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: macro to copy cell contents then remove substring of contents in new cell - Help

    Sounds like you have Option Explicit enabled, but you haven't really defined i.

    Please Login or Register  to view this content.
    should be first line
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: macro to copy cell contents then remove substring of contents in new cell - Help

    To get rid of the error get rid of Range().
    Please Login or Register  to view this content.
    You could also tidy the code up a bit.
    Please Login or Register  to view this content.
    PS Can you add code tags when posting code?
    If posting code please use code tags, see here.

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: macro to copy cell contents then remove substring of contents in new cell - Help

    I rewrote it my own little way:

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    01-27-2011
    Location
    Pittsburgh
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: macro to copy cell contents then remove substring of contents in new cell - Help

    thanks guys....worked as desired with your corrections.....
    Best regards,
    J

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro Problem: Copy contents of cell; search for contents in another workbook
    By jfishstik in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-04-2014, 12:11 PM
  2. [SOLVED] Copy Paste Contents based on contents in designated cell
    By nickmessick1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-11-2014, 01:23 PM
  3. How do I make a cell's contents equal to another cell's contents with macro program?
    By mgmcdevitt in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-15-2005, 04:44 PM
  4. Replies: 1
    Last Post: 07-01-2005, 10:57 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1