How to count cells looking at the first 3 characters of specific text and excluding cells with strikethrough?

[cdude911]

I’ve been able to make macro’s to do one or the other but have struggled to combine them. I need it to be able to check if the first three characters are a match and check if the cell has strikethrough before counting.

 

[RobP]

Hi,
see if this will work for you. I assume your data is in Column A. If not, you can adjust the column number accordingly.
It first counts the number of matches it finds for the first three chars. Then it will search each for a strikethrough and add as necessary to cellcount.

Rgds
Rob

Sub check_strikethrough()

Dim x ,i As Long
Dim cellcount As Long

x = WorksheetFunction.CountIf(Columns(1), "aaa*") 'count how many "aaa" instances (1st three chars match)
'replace "aaa" with your three letter text as required. Wildcard neded

cellcount = 0

For i = 1 To x

    Columns(1).Find(What:="aaa", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
        
    If ActiveCell.Characters.Font.Strikethrough = True Then
    
        cellcount = cellcount + 1
    
    End If

Next i

End Sub

 

[RobP]

apologies - I just saw your heading and realised I had the result the wrong way around.
new code below.

Sub check_strikethrough()

Dim x, result As Long
Dim cellcount As Long

x = WorksheetFunction.CountIf(Columns(1), "bbb*") 'count how many "aaa" instances (1st three chars match)
'replace "aaa" with your three letter text as required. Wildcard neded

cellcount = 0

For i = 1 To x

    Columns(1).Find(What:="bbb", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
       
    If ActiveCell.Characters.Font.Strikethrough = True Then
   
        cellcount = cellcount + 1
   
    End If

Next i

result = x - cellcount

End Sub

 

[cdude911]

apologies - I just saw your heading and realised I had the result the wrong way around.
new code below.

Sub check_strikethrough()

Dim x, result As Long
Dim cellcount As Long

x = WorksheetFunction.CountIf(Columns(1), "bbb*") 'count how many "aaa" instances (1st three chars match)
'replace "aaa" with your three letter text as required. Wildcard neded

cellcount = 0

For i = 1 To x

    Columns(1).Find(What:="bbb", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
      
    If ActiveCell.Characters.Font.Strikethrough = True Then
  
        cellcount = cellcount + 1
  
    End If

Next i

result = x - cellcount

End Sub

Thank you so much for your help. Pardon my stupidity but this is my first time dealing with vba and excel so how would I run this? I also should have specified I need it to output the value into a cell as well

 

[jolivanes]

"I’ve been able to make macro’s to do one or the other"
and
"first time dealing with vba and excel so how would I run this?"
Don't back each other up.

Re: "check if the first three characters are a match"
A match to what? Each other?
Are all the cells to start with these 3 characters or a user supplied string of 3 characters?

Re: "I need it to output the value into a cell as well"
Same row to the right? To the left? 6 Cells to the Right? Under one another in a Column? If so, which Column?

You do have a header in your first row, do you?

We have more questions than you!!!!!!

 

[cdude911]

"I’ve been able to make macro’s to do one or the other"
and
"first time dealing with vba and excel so how would I run this?"
Don't back each other up.

Re: "check if the first three characters are a match"
A match to what? Each other?
Are all the cells to start with these 3 characters or a user supplied string of 3 characters?

Re: "I need it to output the value into a cell as well"
Same row to the right? To the left? 6 Cells to the Right? Under one another in a Column? If so, which Column?

You do have a header in your first row, do you?

We have more questions than you!!!!!!

I should’ve worded that I’ve been able to make them work through copy and paste parts from other peoples macros. I’m familiar with python but not vba or excel so I have a very small understanding of what I’m reading in others vba code.

I need to check if the first 3 characters are a match to a user specific string input into a cell.

I don’t know if it’s possible but I would like to be able to set the output cell as you would with other excel functions.

Sorry but hopefully that clears up some of the info

 

[jolivanes]

Re: "set the output cell as you would with other excel functions"
What does that mean?

Is it OK to get the 3 characters with an InputBox? Or, like you mentioned, from a cell? However, you do not mention which cell. Cell X235 maybe?

 

[cdude911]

Re: "set the output cell as you would with other excel functions"
What does that mean?

Is it OK to get the 3 characters with an InputBox? Or, like you mentioned, from a cell? However, you do not mention which cell. Cell X235 maybe?

So this is the spreadsheet I’m trying to improve. We deal with filing flight plans for aircraft. So what I want to do is to be able to do is count the aircraft type in column e for each day while excluding canceled flight plans that we notate with a strikethrough. I would like the output value in a cell that I would be able to reference to in a function in a different spreadsheet

 

[cdude911]

So this is the spreadsheet I’m trying to improve. We deal with filing flight plans for aircraft. So what I want to do is to be able to do is count the aircraft type in column e for each day while excluding canceled flight plans that we notate with a strikethrough. I would like the output value in a cell that I would be able to reference to in a function in a different spreadsheet

1-Mar
	I/V​	T	CALLSIGN​	ACFT​	ETD​	ATD​	ETE​	ETA​	ATA​
2-Mar
	I/V​	T	CALLSIGN​	ACFT​	ETD​	ATD​	ETE​	ETA​	ATA​
3-Mar
	I/V​	T	CALLSIGN​	ACFT​	ETD​	ATD​	ETE​	ETA​	ATA​
4-Mar
	I/V​	T	CALLSIGN​	ACFT​	ETD​	ATD​	ETE​	ETA​	ATA​
5-Mar

 

[RobP]

Hi,
its often better to be upfront with what you need overal, so that people on here can help in the right way. Half a story will likely result in half an answer, which then leads to more questions, and more work etc.. computers are dumb, so for us to make them work, we have to be very specific with what we are putting IN, and also with what we want OUT. With Excel, that's usually meaning a location / cell or format of some kind. Generally speaking, there's nothing that can't be done in Excel on this forum in some form or another .. if people understand what you want.

Looking at your sheet above, I'm assuming column E contains "ACFT". I also assume based on your statement that you have multiple rows containing aircraft (ACFT ?) on the same date. So the date row has just the date in it, then aircraft types are all below it with not blank rows until the next days date ?

I assume a useful place for the OUTPUT total would be along the Date Row (ie. 1-Mar) in COL E (right above the items you are trying to count ?) but then whoever looks at this value alone wont necessarily know which aircraft type from the list on that specific day it refers to. So you might want to think about that.


To work with macros and VBA, you will need to first expose the "Developer" Tab in Excel (which should generally appear as a heading alongslide your File / Home / Insert / PageLayout etc. drop downs at the top of your page. To expose it, click on File --> Options --> Customise Ribbon --> then in the large right hand box on screen with a list of your headings - you should find "Developer" with an unchecked box against it. Check this box, click OK, and you should have it appear top of your screen as a tab. Click on this tab gets you the Visual Basic button, into which you can paste the code on the blank screen in front of you. You an run the macro from here by hitting F5, or most likely later by clicking on the "Macros" button, and selecting it from the list presented to you.

Hope that gets you started at least ..

Rob