Change the value of cell instead of a formula.

the magician

Active Member

Joined

Nov 9, 2006

Messages

496

• Jan 3, 2007

• #2

Assuming:
A1 contains a zero
A2 contains the formula "=A1"

If you select A2 and Copy, then Paste Special - Values, A2 will now contain a zero.

Surely your post is more about just changing the contents of one cell, so out with it! What's really up here???

 

Upvote 0

O

ohhoonkwon

New Member

Joined

Dec 28, 2006

Messages

15

• Jan 3, 2007

• #3

I have multiple cells and it says "That command cannot be used on multiple selections."

I have a huge list of some error msg and have to sort them out.

When I run an autofilter VBA, the formula containing =A1 doesn;t work because A1 got delete from the filter.

That was why I wanted to know the way of get ridding the formula and saving the exact value..

 

Upvote 0

the magician

Active Member

Joined

Nov 9, 2006

Messages

496

• Jan 3, 2007

• #4

What command are you using that it says "cannot be used"?

By "huge list of some error msg" do you mean cells with #VALUE or #N/A in them, or actual error messages? What action is used to generate them?

 

Upvote 0

O

ohhoonkwon

New Member

Joined

Dec 28, 2006

Messages

15

• Jan 3, 2007

• #5

I ran this code..

Code:

Sub LeaveURLs()
    Application.ScreenUpdating = False
    With Columns("B")
    
        .AutoFilter Field:=1, Criteria1:="<>*/electric/*", Operator:=xlAnd, Criteria2:="<>*/usered/*"
        Range("B2:B" & Rows.Count).EntireRow.Delete
        .AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

It is deleting rows that contains data that I don't need.

Then.. Some rows get deleted then I get alot of #ref errors.

Clarifying myself. I have a big list of "bad link URL"
When I run the code above I get #ref errors

 

Upvote 0

the magician

Active Member

Joined

Nov 9, 2006

Messages

496

• Jan 3, 2007

• #6

Well, if you're getting #ref errors, it's because those cells are "referring" to a cell that is in some way invalid now, probably because it got deleted. I'd kind of think the rows that those cells were in should not be considered "data that I don't need." You know?

 

Upvote 0

O

ohhoonkwon

New Member

Joined

Dec 28, 2006

Messages

15

• Jan 3, 2007

• #7

I found a way to solve this problem.

Select all, copy, paste to notepad.
and then copy from the notepad, paste it to excel and works.

 

Upvote 0

the magician

Active Member

Joined

Nov 9, 2006

Messages

496

• Jan 4, 2007

• #8

Notepad to the rescue, again. How often have we heard that? It's always the simple solution that we overlook at first, eh? Someone should notify Microsoft, make sure they keep it in Vista.

 

Upvote 0

Oorang

Well-known Member

Joined

Mar 4, 2005

Messages

2,071

• Jan 10, 2007

• #9

It sounds like all you are doing here is replacing the formulas with their values. So instead of the notepad jump why not just do:

Code:

ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value

To get rid of any potential formulas?

 

Upvote 0