Search for StrikeThrough in Excel 2

[jcw5107]

I need to be able to select a column (column B always), and for each cell that has a "strikethrough", update the cell in column C (next to the "striked-through" cell) to "UC"..

I have tried building a macro with the recorder, but I am having trouble getting the code to update the very next cell in column C to "UC"....!!

Any suggestions or examples...???
Thanks in advance...!!
jcw5107

 

[SkipVought]

"I have tried building a macro with the recorder, but I am having trouble getting the code to update the very next cell in column C to "UC"....!!"

That's why you need to post the code that you recorded, as i requested. It needs to be customized.


Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[WalkerEvans]

Please post the code that you got using the recorder, as well as any changes you made, so we can see what you've done and get some idea where you may be having problems.



----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long

 

[jcw5107]

Oh man... I'm sorry....!!!

Below is what I have come up with...
Semi-sorta in the ballpark I guess....

Sub Macro4()

Columns("B:B").Select
With Selection.Font
If .Strikethrough = True Then
ActiveCell.FormulaR1C1 = "UC"
End If
End With
End Sub

 

[SkipVought]

That's not exactly what i suggested that you record...

Function IsStrikeThru(r As Range)
    With r.Font
      If .Strikethrough = True Then
        IsStrikeThru = "UC"
      Else
        IsStrikeThru = ""
      End If
    End With
End Function

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[SkipVought]

Sorry, to be more general, it should be...

Function IsStrikeThru(r As Range) As Boolean
    With r.Font
      If .Strikethrough = True Then
        IsStrikeThru = True
      Else
        IsStrikeThru = False
      End If
    End With
End Function

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[SkipVought]

...and in reality, it's this simple...

Function IsStrikeThru(r As Range) As Boolean
    IsStrikeThru = r.Font.Strikethrough
End Function

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[SkipVought]

...and I'd suggest it should be Volatile...

Function IsStrikeThru(r As Range) As Boolean
    Application.Volatile
    IsStrikeThru = r.Font.Strikethrough
End Function

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[jcw5107]

SkipVought,

I'm am tryin' so hard to take your example(s) and implement into my spreadsheet...
I'm just jammed up...!!!
Haven't done much "VB'n" in Excel...!!! (gotta throw my disclaimer out there....)

Is there any way you can hold my hand on this one - a little bit...????
Is your examples checkin' for a strikethrough and updating another cell, or is just checkin' for a strikethrough and then makin' all cells strikethrough = true..???

I'll keep tryin'..!!
Thanks for your help..!!
jcw5107

 

[SkipVought]

Have you ever used any of the IS Functions in Excel. It is designed to do a similar job.

If your column of data is in B starting in Row 2, then in C2...
[tt]
C2: =If(IsStrikeThru(B2),"UC","")
[/tt]

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[jcw5107]

Ok - I'm gettin' somewhere..!!
Thanks for your help & patience on this...!!!

Is there a way to "automate" all this...??
I guess what I'm gonna do is create an Excel template, Paste a bunch of data into the spreadsheet, and click a command button that runs all that you just helped me with.
Any suggestions as to how I would do this..??

I'm still messin' with your examples...!!
Thanks again..!!
jcw5107

 

[SkipVought]

"...click a command button that runs all that you just helped me with."

Do you click a command button when you enter formulas in cells?

"Paste a bunch of data into the spreadsheet..."

I recommend other methods of acquiring data, if at all possible. In many cases you can use MS Query (faq68-5829) to bring data into a sheet from anther workbook or worksheet. This method has the feature that will copy any formulas you have in adjacent columns, down thru all rows of data each time the query is refreshed.

In any case, you can autofill a formula thru thousands of rows of data, with a simple double-click.




Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[jcw5107]

SkipVought,

Ok - I getchya..!!
This whole thing we are doing start in Excel, then we import it into Access. The spreadsheet needs some serious reformatting before it imported. Thats why I'm tryin' to speed things up with some automation or something...

One last question..
In your example of: =IF(IsStrikeThru(B2),"UC","")
When I put this in the cells of column C - it makes every cell Null that is NOT struck thru... How can I re-work this function to just show what is currently in column C when the false part is met, instead of "Nulling" everything out...??

Thanks again Skip..!!
jcw5107

 

[SkipVought]

"When I put this in the cells of column C - it makes every cell Null that is NOT struck thru... How can I re-work this function to just show what is currently in column C..."

You are entering a FORMULA into Column C. The FORMULA is what is CURRENTLY in Column C.

I do not understand???

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[jcw5107]

Ok - Column B is a date. Column C is a "status", usually a code to note that the item is new("N"), updated ("U"), or blank if nothing has changed.
So when I input the function ( =If(IsStrikeThru(B2),"UC","") ) in C2, and then dran-n-drop all the way down to the bottom of the spreadsheet - everything works fine except for it clearing the cells that are NOT affected by the IsStrikeThru function...
I need to be able to leave these cells alone because there is a status code there that needs to be recorded....

I may be goin' about this the wrong way...!!!
Sorry for any confusion..!!
jcw5107

 

[SkipVought]

Then you need to do this in another column, say Column D and then Column D becomes the NEW status column...
[tt]
D2: =If(IsStrikeThru(B2),"UC",C2)

[/tt]


Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]