I need a simple code to resolve the following

rufuxinix

New Member
Joined
Jan 23, 2019
Messages
39
Office Version
  1. 2019
Platform
  1. Windows
Good morning

This should be easily done if not in Excel tehn in VBA

I need a simple code to resolve the following

I have a table with cells filled with numbers, or words, and cells which are empty, randomly.

I want to automatically depict next to each row the following example. row as 121, where the contents of that row would be

Example:

The contents of the row would be as below and the result would show, on a cell net to the row, 121

Filled cell / Empty cell /Filled Cell / Filled cell / empty cell / Filled cell = 121

Note: empty cells can be consecutively more than one and the result would still be 121, such as below

Filled cell / Empty cell /Filled Cell / Filled cell / empty cell / empty cell / Filled cell = 121

Some other examples

Filled cell / Filled Cell / Filled cell / empty cell / empty cell / Filled cell = 31
Filled cell /Filled Cell / Filled cell
/ empty cell / empty cell / empty cell = 3
empty cell / empty cell / Filled cell /Filled Cell / Filled cell = 3


Hope someone can help.

Thank you for your attention

Regards
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
This will work
Book1
ABCDEFGH
2abbc121
3aaab31
4bbb3
Sheet10
Cell Formulas
RangeFormula
H2:H4H2=CONCAT(IFERROR(1/(1/FREQUENCY(IF($A2:$F2<>"",COLUMN($A2:$F2)),IF($A2:$F2="",COLUMN($A$2:$F$2)))),""))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 
Upvote 0
Thanks Jason, but I'm not getting a result. Press enter and the cell displays blank

1590313495789.png
 
Upvote 0
Sub cell_count()
Dim cell As Range, N As Long
'data in range ("a1:p4)
'column"Q"must be empty
'column "R" is used for final answer


Range("r:r").Clear
For Each cell In Range("a1:q1")
If IsEmpty(cell) = False Then
N = N + cell.Cells.Count
ElseIf N = 0 Then
Else
Range("r1").Value = Range("r1").Value & N
N = 0
End If
Next cell

For Each cell In Range("a2:q2")
If IsEmpty(cell) = False Then
N = N + cell.Cells.Count
ElseIf N = 0 Then
Else
Range("r2").Value = Range("r2").Value & N
N = 0
End If
Next cell

For Each cell In Range("a3:q3")
If IsEmpty(cell) = False Then
N = N + cell.Cells.Count
ElseIf N = 0 Then
Else
Range("r3").Value = Range("r3").Value & N
N = 0
End If
Next cell

For Each cell In Range("a4:q4")
If IsEmpty(cell) = False Then
N = N + cell.Cells.Count
ElseIf N = 0 Then
Else
Range("r4").Value = Range("r4").Value & N
N = 0
End If
Next cell

End Sub
 
Upvote 0
Make sure that you enter the formula and confirm the array only in F1, then fill it down. If you enter the formula into all of the cells then confirm the array as a whole then it will possibly give the incorrect results that you have seen.
 
Upvote 0
Make sure that you enter the formula and confirm the array only in F1, then fill it down. If you enter the formula into all of the cells then confirm the array as a whole then it will possibly give the incorrect results that you have seen.
Done!!
Thanks, Jason, your help is highly appreciated, I can now move on...
Bests regards.
1590337993152.png
 
Upvote 0
Hi J
I came up with a need around this which i cannot work out.
How could I find out how many variations there are in each row?
Say, how many variations of 111 in the example above? 135 only.
But if I have 8 columns, there will be 16 variations: 135, 136, 137, 138, 146, 147, 148, 157, 158, 246, 247, 248, 257, 258, 357, 358.
Is there a formula i can use to quickly pick how many there would be?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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