Copy-Paste COUNTIF

[ferdo]

Hi,

I have data coming from another macro in this format
A B C D....n
1
2 A1 x x...
3 A2 x x x...
.....................
A(i) x x...
i+1 Q

In cell Q, I would like to put a COUNTIF formula to see how many times the x letter will appear and then copy-paste the formula in row i+1, column C up to n, all these things in a macro.

For a fixed value of i and n, I Know how to do it but for different values, I'm lost...

Thanks for the help.

Regards
Fernando

 

[ColourfulFigsnDiags]

Sorry I dont understand what you are asking? "a COUNTIF formula to see how many times the x letter will appear" ... count how many times the x letter appears where??? Can you explain what you are after a bit more clearly?

Perhaps explain what you would do for a fixed i and n and then explain what you want the pasted formula to look like?

Read the Eng-Tips Site Policies at FAQ731-376

 

[ferdo]

Hi,

Thanks for reply, I found the solution.

Instead of using for example

Range(A14:G14).Select

I have to use

myNum = Application.InputBox("Enter a number ")

k = myNum

rang = "B" & CStr(k)
rang1 = "R" & CStr(k)

Range(rang, rang1).Select

My problem was the comma between rang and rang1.....
Thanks anyway.

Regards
Fernando

 

[joerd]

Next time, try this:

    Dim r As Range, myNum as String, k as Long

    myNum = Application.InputBox("Enter a number ")
    k = CLng(myNum)
    Set r = Range(Cells(k, 2), Cells(k, 18))

No need to go into string manipulation. Also, instead of using Select you can set a range object and use that to manipulate your range, for example:

r.Interior.ColorIndex = 3

This usually gives cleaner and faster code.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[ferdo]

Thanks Joerd for the advice, I will give a try next days.

Regards
Fernando