×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Clearing indirect references

Clearing indirect references

Clearing indirect references

(OP)
It can be a nusiance to copy eqns from cell to cell so this sometimes helps:
'Changes a cell to read the same as the cell it's refering to
Sub ClearIndirectRef()
   With Selection
      FRow = .Row
      RowCt = .Rows.Count
      LRow = FRow + RowCt - 1
      FCol = .Column
      ColCt = .Columns.Count
      LCol = FCol + ColCt - 1
   End With
   
   If RowCt > 3 Or ColCt > 3 Then
      a = MsgBox("Are you sure?", 1, "More than 3 rows selected")
      If a = vbCancel Then Exit Sub
   End If
   
   For r = FRow To LRow
      For c = FCol To LCol
         Cells(r, c).Activate
         t1 = Cells(r, c).Formula
         If Len(t1) > 1 Then
            If left(t1, 1) = "=" Then
               a = Mid(t1, 2)
               If Len(a) > 1 Then
                  t2 = Range(a).Formula
                  Cells(r, c).Formula = t2
               End If
            End If
         End If
      Next
   Next
End Sub

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources