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



[TABLE="width: 842"]
<tbody>[TR]
[/TR]
</tbody>[/TABLE]

<tbody>
[TD="class: xl119"]
[/TD]
[TD="class: xl119, width: 64"]
[/TD]
[TD="class: xl119, width: 64"]
[/TD]
[TD="class: xl119, width: 64"]
[/TD]
[TD="class: xl119, width: 64"]
[/TD]
[TD="class: xl119, width: 64"]
[/TD]
[TD="class: xl119, width: 64"]
[/TD]
[TD="class: xl145, width: 62"] train 1 [/TD]
[TD="class: xl145, width: 76"] train 2 [/TD]
[TD="class: xl145, width: 64"] train 3 [/TD]
[TD="class: xl145, width: 64"] train 4 [/TD]
[TD="class: xl145, width: 64"] train 5 [/TD]
[TD="class: xl145, width: 64"] train 6 [/TD]

[TD="class: xl119"]
[/TD]
[TD="class: xl119"]
[/TD]
[TD="class: xl119"]
[/TD]
[TD="class: xl119"]
[/TD]
[TD="class: xl119"]
[/TD]
[TD="class: xl119"]
[/TD]
[TD="class: xl119"]
[/TD]
[TD="class: xl145, width: 62"] ABC123 [/TD]
[TD="class: xl145, width: 76"] ABC124 [/TD]
[TD="class: xl145, width: 64"] ABC125 [/TD]
[TD="class: xl145, width: 64"] ABC126 [/TD]
[TD="class: xl145, width: 64"] ABC127 [/TD]
[TD="class: xl145, width: 64"] ABC128 [/TD]

[TD="class: xl223"] Division [/TD]
[TD="class: xl223"] data [/TD]
[TD="class: xl223"] more data [/TD]
[TD="class: xl223"] Manager [/TD]
[TD="class: xl223"] Employee [/TD]
[TD="class: xl223"] Emp Id [/TD]
[TD="class: xl223"] Pct Comp [/TD]
[TD="class: xl144"] Push [/TD]
[TD="class: xl144"] Push [/TD]
[TD="class: xl119"] Question [/TD]
[TD="class: xl144"] Push [/TD]
[TD="class: xl144"] Push [/TD]
[TD="class: xl119"] Question [/TD]

[TD="class: xl216"] hr [/TD]
[TD="class: xl217"] west [/TD]
[TD="class: xl217"] north [/TD]
[TD="class: xl217"] Fred [/TD]
[TD="class: xl217"] emp1 [/TD]
[TD="class: xl148, align: right"] 11 [/TD]
[TD="class: xl221, align: right"] 89.5%
[/TD]
[TD="class: xl121"] NR
 [/TD]
[TD="class: xl121"] Complete [/TD]
[TD="class: xl121"]
[/TD]
[TD="class: xl121"] Complete [/TD]
[TD="class: xl121"]
[/TD]
[TD="class: xl121"] Complete [/TD]

[TD="class: xl216"] hr [/TD]
[TD="class: xl217"] west [/TD]
[TD="class: xl217"] north [/TD]
[TD="class: xl217"] wilma [/TD]
[TD="class: xl217"] emp2 [/TD]
[TD="class: xl148, align: right"] 12 [/TD]
[TD="class: xl221, align: right"] 96.9% [/TD]
[TD="class: xl108"] NR
 [/TD]
[TD="class: xl108"] Complete [/TD]
[TD="class: xl108"]
[/TD]
[TD="class: xl108"] Complete [/TD]
[TD="class: xl108"] Complete [/TD]
[TD="class: xl108"]
[/TD]

[TD="class: xl216"] hr [/TD]
[TD="class: xl217"] coast [/TD]
[TD="class: xl217"] north [/TD]
[TD="class: xl217"] barney [/TD]
[TD="class: xl217"] emp3 [/TD]
[TD="class: xl148, align: right"] 13 [/TD]
[TD="class: xl221, align: right"] 96.4% [/TD]
[TD="class: xl108"]
[/TD]
[TD="class: xl108"] Complete [/TD]
[TD="class: xl108"]
[/TD]
[TD="class: xl108"] Complete [/TD]
[TD="class: xl108"] Complete [/TD]
[TD="class: xl108"]
[/TD]

[TD="class: xl216"] hr [/TD]
[TD="class: xl217"] west [/TD]
[TD="class: xl217"] north [/TD]
[TD="class: xl217"] Fred [/TD]
[TD="class: xl217"] emp4 [/TD]
[TD="class: xl148, align: right"] 14 [/TD]
[TD="class: xl221, align: right"] 100.0% [/TD]
[TD="class: xl108"]
[/TD]
[TD="class: xl108"] Complete [/TD]
[TD="class: xl108"]
[/TD]
[TD="class: xl108"] Complete [/TD]
[TD="class: xl108"] Complete [/TD]
[TD="class: xl108"]
[/TD]

[TD="class: xl216"] hr [/TD]
[TD="class: xl217"] west [/TD]
[TD="class: xl217"] north [/TD]
[TD="class: xl217"] wilma [/TD]
[TD="class: xl217"] emp5 [/TD]
[TD="class: xl148, align: right"] 15 [/TD]
[TD="class: xl221, align: right"] 100.0% [/TD]
[TD="class: xl108"]
[/TD]
[TD="class: xl108"]
[/TD]
[TD="class: xl108"]
[/TD]
[TD="class: xl108"] Complete [/TD]
[TD="class: xl108"] Complete [/TD]
[TD="class: xl108"]
[/TD]

[TD="class: xl216"] hr [/TD]
[TD="class: xl217"] west [/TD]
[TD="class: xl217"] north [/TD]
[TD="class: xl217"] barney [/TD]
[TD="class: xl217"] emp6 [/TD]
[TD="class: xl148, align: right"] 16 [/TD]
[TD="class: xl221, align: right"] 100.0% [/TD]
[TD="class: xl108"]
[/TD]
[TD="class: xl108"]
[/TD]
[TD="class: xl108"]
[/TD]
[TD="class: xl108"] Complete [/TD]
[TD="class: xl108"] Complete [/TD]
[TD="class: xl108"]
[/TD]

[TD="class: xl216"] hr [/TD]
[TD="class: xl217"] west [/TD]
[TD="class: xl217"] north [/TD]
[TD="class: xl217"] Fred [/TD]
[TD="class: xl217"] emp7 [/TD]
[TD="class: xl148, align: right"] 17 [/TD]
[TD="class: xl221, align: right"] 58.8% [/TD]
[TD="class: xl108"]
[/TD]
[TD="class: xl108"]
[/TD]
[TD="class: xl108"]
[/TD]
[TD="class: xl108"] Complete [/TD]
[TD="class: xl108"] 07/31/17 [/TD]
[TD="class: xl108"]
[/TD]

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

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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,224,827
Messages
6,181,197
Members
453,021
Latest member
pingpong7117

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