Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    I found a Maco in a VBA example and I tired to modify it to do a different task

    The original Macro looked for the number 2 in the range and every time it finds 2 it turned the cell Grey.

    What I want to do is find any formula with an "!" in it and change the formula to a value.

    The below works fine until it hits the last cell in the range with ! then it issues a warning message and does not finish.

    Can you please assist with the below or suggest a better VBA solution.

    Thanks in advance.

    Sub MyChangeIt()
    Dim C As Range
    With ActiveSheet.Range("A1:HA500")
    Set c = .Find("!", LookIn:=xlFormulas)
    If Not c Is Nothing Then
    FirstAddress = c.Address
    Do
    c.Copy
    c.PasteSpecial Paste:=xlValues
    Application.CutCopyMode = False
    Set c = .FindNext(C)
    Loop While Not c Is Nothing and c.Address <> firstAddress
    End If
    End With
    End Sub

    Regards,

    Tom Duthie

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    What does the warning message say? The following version works without errors for me

    Code:
    Sub MyChangeIt()
      Dim C As Range
      Dim firstAddress As String
      With ActiveSheet.Range("A1:HA500")
    	Set C = .Find("!", LookIn:=xlFormulas)
    	If Not C Is Nothing Then
    	  firstAddress = C.Address
    	  Do
    		C.Copy
    		C.PasteSpecial Paste:=xlValues
    		Application.CutCopyMode = False
    		Set C = .FindNext(C)
    		If C Is Nothing Then Exit Do
    	  Loop While Not C.Address <> firstAddress
    	End If
      End With
    End Sub

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    This line:
    Code:
    Loop While Not c Is Nothing and c.Address <> firstAddress
    would cause an error when c is nothing, because you can't access the Address property. Hans' version should remove that problem.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    [quote name='rory' post='771344' date='20-Apr-2009 08:23']This line:
    Code:
    Loop While Not c Is Nothing and c.Address <> firstAddress
    would cause an error when c is nothing, because you can't access the Address property. Hans' version should remove that problem.[/quote]

    That is the line that was causing the issue. The new code now works without an error message.

    Thank you for the help.

    Tom Duthie

  5. #5
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    [quote name='duthiet' post='771351' date='20-Apr-2009 09:25']That is the line that was causing the issue. The new code now works without an error message.

    Thank you for the help.

    Tom Duthie[/quote]

    Spoke too soon. Hans VBA avoids the error message but does not evaluate all cells and change their values
    the below code now evaluates all the cells, changes those with "!" and exits without an error message.
    These two lines are what I needed.

    If C Is Nothing Then Exit Do
    Loop While Not C Is Nothing And C.Address <> firstAddress

    Full VBA

    Sub MyChangeIt()
    Dim C As Range
    Dim firstAddress As String
    With ActiveSheet.Range("A1:HA500")
    Set C = .Find("!", LookIn:=xlFormulas)
    If Not C Is Nothing Then
    firstAddress = C.Address
    Do
    C.Copy
    C.PasteSpecial Paste:=xlValues
    Application.CutCopyMode = False
    Set C = .FindNextŠ
    If C Is Nothing Then Exit Do
    Loop While Not C Is Nothing And C.Address <> firstAddress
    End If
    End With
    End Sub

    Once again thanks for all the help

    Tom Duthie

Posting Permissions

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