Ask Question Forum:
Model Library:2025-02-08 Updated:A.I. model is online for auto reply question page
C
O
M
P
U
T
E
R
2
8
Show
#
ASK
RECENT
←
- Underline
- Bold
- Italic
- Indent
- Step
- Bullet
- Quote
- Cut
- Copy
- Paste
- Table
- Spelling
- Find & Replace
- Undo
- Redo
- Link
- Attach
- Clear
- Code
Below area will not be traslated by Google,you can input code or other languages
Hint:If find spelling error, You need to correct it,1 by 1 or ignore it (code area won't be checked).
X-position of the mouse cursor
Y-position of the mouse cursor
Y-position of the mouse cursor
Testcursor
caretPos
Attachment:===
Asked by RWayneH
at 2024-07-23 08:48:53
ModifyPoint:500 Replies:14 POST_ID:829081USER_ID:11983
Topic:
Microsoft Excel Spreadsheet Software;;
How do I do the following in VBA. I can explain in English what I would like it to do.
Goto column O (letter not zero) of the active sheet, and count the number of times the letter “S†is there. Place the result in cell A6 even if it is zero.
Then
Goto column Q and tell if there is a value of “WD†in that column? If so, place that value in cell A7, if does not exist, leave A7 blank.
Goto column O (letter not zero) of the active sheet, and count the number of times the letter “S†is there. Place the result in cell A6 even if it is zero.
Then
Goto column Q and tell if there is a value of “WD†in that column? If so, place that value in cell A7, if does not exist, leave A7 blank.
Expert: test test replied at 2026-04-13 23:24:30
IF(COUNTIF(Q:Q,"WD*")=0,"","WD")
Expert: Glenn Ray replied at 2024-07-23 11:30:42
You're welcome. Sorry about the oversight on your original request.
Author: RWayneH replied at 2024-07-23 11:29:43
Thanks works great!!
Accepted Solution
Expert: Glenn Ray replied at 2024-07-23 11:02:35
500 points EXCELLENT
I apologize; I didn't read your original instruction properly. Change the formula for A7 to this:
=IF(COUNTIF(Q:Q,"WD*")=0,"","WD")
=IF(COUNTIF(Q:Q,"WD*")=0,"","WD")
Author: RWayneH replied at 2024-07-23 11:01:02
Opps not sure if it is working.... the rule for column Q is that the value WD has to exist anywhere in Q. We are not counting that column.. If it is there, place WD in A7, if not leave it blank.
Author: RWayneH replied at 2024-07-23 10:58:47
Nevermind got it.. Looks like this is going to work!!
Author: RWayneH replied at 2024-07-23 10:57:34
How does the formula in A7 chg if I want the result to equal WD? It is giving me a number. (original req)
Expert: Glenn Ray replied at 2024-07-23 10:48:57
RWayneH,
This is because the values in column O and Q are not trimmed; there are trailing spaces after all values (six characters in column O; nine in column Q).
Change the formulas as follows to account for this:
A6: =COUNTIF(O:O,"S *") (<--note extra space after the S!)
A7: =IF(COUNTIF(Q:Q,"WD*")=0,"",COUNTIF(Q:Q,"WD*"))
Regards,
-Glenn
This is because the values in column O and Q are not trimmed; there are trailing spaces after all values (six characters in column O; nine in column Q).
Change the formulas as follows to account for this:
A6: =COUNTIF(O:O,"S *") (<--note extra space after the S!)
A7: =IF(COUNTIF(Q:Q,"WD*")=0,"",COUNTIF(Q:Q,"WD*"))
Regards,
-Glenn
Author: RWayneH replied at 2024-07-23 10:36:57
If there is a instance of WD in column Q, it some return the value of "WD" to that cell. No matter how many times it is there.
Author: RWayneH replied at 2024-07-23 10:32:33
for some reason I have an "S" in column O that it is not seeing. Returning 0 ?? and there is a WD in column Q and the result is blank?? Looks like the formulas are not returning the proper result? What am I doing wrg?
Author: RWayneH replied at 2024-07-23 10:24:19
ok.. let me throw this around a bit and see if I can get the results to popular within my For Selection loop.
Expert: Glenn Ray replied at 2024-07-23 09:06:12
Agreeing with Mitchell. Based on the description of the data, it sounds like the entire cell value with either be "S" (for column O) or "WD" (in column Q). In this case, the following formulas could be inserted in A6 and A7:
A6: =COUNTIF(O:O,"S")
A7: =IF(COUNTIF(Q:Q,"WD")=0,"",COUNTIF(Q:Q,"WD"))
However, if you're looking for occurrences of either value within cells (i.e., part of a larger string of characters), then you'll need different formulas.
Regards,
-Glenn
A6: =COUNTIF(O:O,"S")
A7: =IF(COUNTIF(Q:Q,"WD")=0,"",COUNTIF(Q:Q,"WD"))
However, if you're looking for occurrences of either value within cells (i.e., part of a larger string of characters), then you'll need different formulas.
Regards,
-Glenn
Expert: duncanb7 replied at 2024-07-23 09:05:24
Mitchell is correct, I think author can search a word of "countif Excel VBA" in internet, there is a lot tutorial as following sites , and he will get it finally
http://www.techonthenet.com/excel/formulas/countif.php
Duncan
http://www.techonthenet.com/excel/formulas/countif.php
Duncan
Expert: Mitchell Milligan replied at 2024-07-23 09:01:28
Maybe using the CountIF in Excel is a good place to start looking? If it is Excel, it has a built in feature for this called the =countif command, and then set the range to be counted.
