Adding results to each other

[maxon]

Hello Guys,

I probably have a silly question but it blocked me totally

I have a below formula, which is counting the data from one sheet:

Dim IMPORTcount As Integer
IMPORTcount = Sheets("IMPORT").Cells(ROWS.Count, "A").End(xlUp).Row

and I have also second formula, which is counting X's from second sheet:

With Sheets("DBCR").Range("O1")
    .FormulaR1C1 = "=COUNTIF(R[1]C:R[1999]C,""X"")"
    .Font.Bold = True
    .Interior.ColorIndex = 6
    .HorizontalAlignment = xlCenter
End With

How can I add the first results to the second formula, so COUNTIF(R[1]C:R[1999]C,""X"")" + IMPORTcount?

And second question - is it possible to count X's in cells if for example I have in cell XXXXX <- I would like to count it five times, so result should be 5, not 1 - excel can do that?

Thank you
Max

 

[bukimi]

Try with "Application.Worksheetfunction" instead of FormulaR1C1

Then, your O1 Cell will contain the sum like that. (try this code)

Dim Rng
Set Rng = [COLOR=#333333]Range("A1:A1999")<change range="" to="" where="" you="" look="" for="" your="" x's
 
</change>[/COLOR]

Dim IMPORTcount As Integer
IMPORTcount = Sheets("IMPORT").Cells(ROWS.Count, "A").End(xlUp).Row
[COLOR=#333333]Sheets("DBCR").Range("O1").Value = [COLOR=#333333]IMPORTcount + [/COLOR]Application.Worksheetfunction.Countif(Rng, "X")[/COLOR]

Of course set your Range (Rng) to where you look for your X's

For your second question (counting within cell)... I don't know unfortunately.

 

[bukimi]

For your second question I found a complete solution here:
Excel formula: Count specific characters in a cell | Exceljet
It explains how to make a formula counting specified characters in a cell. I won't copy this solution here, as it's not mine.

 

[maxon]

Thanks bukimi for your reply.
Problem is that I need to insert active formula, which will be counting X's when users are inserting them, thats why I used R1C1 formula. Application.WorksheetFunction.CountIf counts during macro working...
Maybe you have any idea how to build a code this way and connect it with the formula from your second reply?

 

[maxon]

I think that I have to add new column, where will be inserted formula =LEN(O2), which will present number of characters in cells in column O and then next formula above them to sum all of the results, but its also need to be active formula. Hmmm... am I right? but how to connect it with the ImportCount variable.

 

[bukimi]

You can't have a variable in an active formula as you may think. The only solution is to replace variable with formula, too.
(another workaround is having a macro to calculate variables after any cell changes, but it's not very efficient solution)

IMPORTCount checks which Row is the last row with any data?
Then, again taken from Exceljet:

           =[URL="https://exceljet.net/excel-functions/excel-min-function"]MIN[/URL]([URL="https://exceljet.net/excel-functions/excel-row-function"]ROW[/URL](rng))+[URL="https://exceljet.net/excel-functions/excel-rows-function"]ROWS[/URL](rng)-1

Where rng is your range. As far as I can see it's whole column A in another sheet.
Add the code above (put it in brackets) with the solution I linked earlier (which is also active formula).

Let me know if it's helpful. It should work without VBA. I have some difficulties with testing this solution by myself, because 1) I don't have data sheet 2) My Excel is in another language and I need to translate all formulas to use them...

 

[maxon]

Thanks bukimi and sorry for reply after 3 weeks but i had no access to my computer. I have finally changed to formula into

=SUMPRODUCT(LEN(O2:O2000))+COUNTA(IMPORT!A:A)

and it works actively counting current number of signs in O column and also number of lines in other sheet. Once again thanks for you time and advices!

 

[maxon]

Its maybe an easy question but why if I use following code:

Sheets(1).Range("O1").FormulaR1C1 = "=SUMPRODUCT(LEN(O2:O2000))+COUNTA(IMPORT!A:A)"

the result of macro is adding ' sign like this:

"=SUMPRODUCT(LEN([COLOR=#ff0000]'[/COLOR]O2:O2000[COLOR=#ff0000]'[/COLOR]))+COUNTA(IMPORT!'A:A')"

so I have to use

=SUMPRODUCT(LEN(R[1]C:R[19]C))+COUNTA(IMPORT!C[-14])

thanks

 

[maxon]

Case solved, I have found solution on forum, I forgot that I used r1c1 formula, instead of just formula. Thanks!