VBA - Find X / Paste Special as Value

[Sundae]

Hi All,

I am new to VBA and need some assistance. I need to store this in VBE as it will become part of my application:

I need to find a specific value in Column A, ie: "Number", if Number is found in Column A in cell A5, then the formula in cell B5 needs to be pasted as a value. I would want the code to repeat this process for each string of "Number" in column A.

I hope I have explained this thoughtfully.

Thanks,

 

[Rick Rothstein]

I need to find a specific value in Column A, ie: "Number", if Number is found in Column A in cell A5, then the formula in cell B5 needs to be pasted as a value. I would want the code to repeat this process for each string of "Number" in column A.

Some questions...

1) The word "Number" that is found in Column A... where exactly did you want its corresponding Column B value pasted to? On top of itself? One column over and on the same row that "Number" is found? In a contiguous list in some unused column?

2) Is the word "Number" the only text that will be in the cell, or could it be embedded among other text?

3) What is in Column A... constant values or formulas?

 

[Sundae]

Thanks for the quick reply....

Some questions...

1) The word "Number" that is found in Column A... where exactly did you want its corresponding Column B value pasted to? On top of itself? One column over and on the same row that "Number" is found? In a contiguous list in some unused column?

A: On top of itself.

2) Is the word "Number" the only text that will be in the cell, or could it be embedded among other text?

A: Only text in cell, matchcase = true

3) What is in Column A... constant values or formulas?

A: constant values

 

[Jose_Chasez]

Try this:

Sub test()
Dim x As Long
Dim lastrow As Long
lastrow = ActiveSheet.UsedRange.Rows.Count
For x = 1 To lastrow
    If Range("A" & x).Value = "Number" Then
        Range("B" & x).Copy
        Range("B" & x).Select
        Selection.PasteSpecial Paste:=xlPasteValues
    End If
Next x
End Sub

 

[Sundae]

Thank you so much - this works perfectly!!!

 

[Rick Rothstein]

Thank you so much - this works perfectly!!!

Based on the postings so far, can we conclude that Column B contains formulas and that some of those formulas are displaying numerical values while the rest are either displaying the empty string ("") or some other non-numerical text? Also, can we conclude that for every number being displayed in Column B, that Column A is displaying the word "Number"? If so, then you can convert those formula-numbers to real numbers using two lines of code...

On Error Resume Next
Columns("B").SpecialCells(xlFormulas, xlNumbers).Value = Columns("B").SpecialCells(xlFormulas, xlNumbers).Value

 

[Sundae]

Thanks. I am going to stick with the initial code you provided, as the most recent assumptions you made are not accurate.

Thank you again!

 

[Rick Rothstein]

Thanks. I am going to stick with the initial code you provided, as the most recent assumptions you made are not accurate.!

Actually, I had not posted any code previously; instead, I asked you some questions and then completely missed that you had answered those question. Based on your answers, this code should do what you want and I think it will do it faster than Jose's code (no looping is involved with my code)...

Sub ProcessNumberCells()
  Columns("A").Replace "Number", "=Number", xlWhole
  On Error Resume Next
  With Columns("A").SpecialCells(xlFormulas)
    .Offset(, 1).Value = .Offset(, 1).Value
    .Replace "=", "", xlPart
  End With
End Sub

 

[Sundae]

My apologizes - I did not realize that Jose had also responded - I will be more careful in the future.

Thank you for the vba code - you are awesome!