PDA

View Full Version : [SOLVED] Replace certain cells in rows with values from same rows but in different column



1819
01-28-2017, 08:41 PM
I have attached a sample worksheet which works to overwrite the values in column A if there is a value in the adjacent cell in column B.

So it copies and pastes all values in column B to the adjacent cell in column A. It ignores blanks.

The code I have used to select the cells which contain values in column B is:



Option Explicit

Sub Selector()

'5 - Overwrite cells in column C where the row has value from column M

'PURPOSE: Selects only cells with values or formulas and excludes blank values (ie ="" or +"")
'AUTHOR: Rick Rothstein (MrExcel MVP)
'SOURCE: www.TheSpreadsheetGuru.com

Dim rng As Range
Dim UnusedColumn As Range
Dim Target As Range
Dim Cancel As Boolean

With ActiveSheet
Set rng = Range("B1:B3000")

'Find a column with nothing in it
Set UnusedColumn = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).EntireColumn.Offset(0, 1)

'Create temporary calculation column to determine which cells to select (marked by an X)
Intersect(rng.EntireRow, UnusedColumn) = Evaluate("IF(" & rng.Address & "="""","""",""X"")")

'Make Selection
Intersect(UnusedColumn.SpecialCells(xlConstants).EntireRow, rng.EntireColumn).Select

'Remove Temporary Blank Calculations
UnusedColumn.Clear

End With

End Sub


and the code to overwrite column A is:



Option Explicit

Sub Macro1()
'
' Macro1 Macro
'

'
Columns("A:B").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$B$3000").AutoFilter Field:=2, Criteria1:="<>"
Range("B1:B367").Select
Selection.Copy
Application.CutCopyMode = False
Columns("A:A").Select
Selection.Copy
Application.CutCopyMode = False
ActiveSheet.Range("$A$1:$B$3000").AutoFilter Field:=2
Columns("A:A").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub


Unfortunately I need to use all this code in a much larger spreadsheet, where column A in the sample is at column C, and column B in the sample is at column M.
There are also 21,000 rows of data.

I simply cannot get the code in the sample file to work in the larger spreadsheet. What changes do I need to make to the code to make it run properly?

Many thanks.

18177

Paul_Hossler
01-29-2017, 09:16 AM
I think I understand the question

I'd just keep if simple. If this is too slow, there's probably ways to speed it up




Option Explicit

Sub CopyOver()
Dim rSrc As Range, rDest As Range, rCell As Range

Set rDest = ActiveSheet.Columns(3) ' <------ change as needed
Set rSrc = ActiveSheet.Columns(13) ' <------ change as needed

On Error GoTo NiceExit
For Each rCell In rSrc.SpecialCells(xlCellTypeConstants).Cells
Call rCell.Copy(rDest.Cells(rCell.Row))
Next
NiceExit:


End Sub

1819
01-29-2017, 10:54 AM
Thanks Paul, another incredibly useful answer for me (you have been terrific answering my questions).

The "On Error" trap was causing problems, in that I had some #VALUE!s in the cells, so I simply put
Cells.Replace "#VALUE!", "", xlWhole in front and took out the error trap, and it works very well.

Speed's not a material concern.

Thank you again.