Set value in specific columns of an already selected cell range

ABURN

New Member
Joined
Mar 16, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi all

I'm trying to find the right code to overwrite the values (regardless of what they are, so find/replace does not work) in a specified column (say column W) of an already selected range of cells with a static value number (say "100"). I've tried various things but nothing seems to work! I don' want the code to replace the value in all cells in that column, just in the range of cells (rows) selected, which were selected from having being pasted in in the previous part of the VBA below.

Here is the VBA I have already which selects rows of entries in a journal and then copies and pastes those same rows below the last populated row. The idea is that the copied and pasted set is the reversals for the journal (at the end of this code the applicable rows where the value in column W needs to be replaced by 100 are already selected):

VBA Code:
Sub Reverse_Charges_Against_CandB()

    Application.ScreenUpdating = False
    Sheets("BL Core Posting Import").Select
    Rows("5:5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    
    Dim lastrow As Long
    lastrow = Range("O65536").End(xlUp).Row
    Sheets("BL Core Posting Import").Activate
    Cells(lastrow + 1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Application.ScreenUpdating = True
End Sub
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,703
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub Reverse_Charges_Against_CandB()
    Dim RwCount As Long
    
    Application.ScreenUpdating = False
    Sheets("BL Core Posting Import").Select
    Rows("5:5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    RwCount = Selection.Rows.count
    Selection.Copy
    
    Dim lastrow As Long
    lastrow = Range("O65536").End(xlUp).Row
    Sheets("BL Core Posting Import").Activate
    Cells(lastrow + 1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Intersect(Rows(lastrow + 1).Resize(RwCount), Range("W:W")).Value = 100
    Application.ScreenUpdating = True
End Sub
 
Solution

Forum statistics

Threads
1,176,085
Messages
5,901,295
Members
434,886
Latest member
qazibelal

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top