VBA Script last cell/column with remove replace copy formatting

L

lefty38

Board Regular
Joined
Oct 27, 2005
Messages
85
Hello - this site has always been a great source of help
once again
i am looking for VB code that will select from cell H4 out to the last column/last cell
then with that selection perform two functions
find null values and replace with NR
copy the conditional formatting of H4 and paint brush to the last cell / column
excel version 2013
cells can contain null values
employee id will always have a value
again thank you




<tbody>
</tbody>







train 1train 2train 3train 4train 5train 6







ABC123ABC124ABC125ABC126ABC127ABC128
Divisiondatamore dataManagerEmployeeEmp IdPct CompPushPushQuestionPushPushQuestion
hrwestnorthFredemp11189.5%
NR
Complete
Complete
Complete
hrwest northwilmaemp21296.9%NR
Complete
CompleteComplete
hrcoastnorthbarneyemp31396.4%
Complete
CompleteComplete
hrwest northFredemp414100.0%
Complete
CompleteComplete
hrwest northwilmaemp515100.0%


CompleteComplete
hrwest northbarneyemp616100.0%


CompleteComplete
hrwest northFredemp71758.8%


Complete07/31/17

<tbody>
</tbody>
 
Sort by date Sort by votes
That sounds as though the cell is blank.
 
Upvote 0

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
A little background
data is copy pasted from an access crosstab query

if I run the script it returns the error: '1004' No cells were found

if I double click on the 'empty' cell or individually a group of cells ==> the script returns within the "double clicked" cells "NR"


If I select a range of blank cells and "cntl H" ==> "find & replace"
replace: ==> blank (null)
replace with: ==> NR
the selected cell are replaced with NR

so how come "find & replace" works where the VB Script does not?
 
Upvote 0
You may have NullStrings in there, try
Code: 
Sub test()

    Dim Rng As Range
    Dim UsdCols As Long
    Dim UsdRws As Long

    UsdRws = Cells.find("*", lookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    UsdCols = Cells.find("*", lookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    
    Set Rng = Range("H4", Cells(UsdRws, UsdCols))
    Rng.Value = Rng.Value
    Rng.SpecialCells(xlBlanks).Value = "NR"
    Range("H4").Copy
    Rng.PasteSpecial Paste:=xlPasteFormats
End Sub
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,030
Messages
6,122,762
Members
449,095
Latest member
m_smith_solihull

Share this page

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
Back
Top