By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,097 Members | 1,405 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,097 IT Pros & Developers. It's quick & easy.

Excel Macro

P: 22
Hi,

Really simple query here. I want to write a macro that will work in this order:

1)Count the amount of cells in column A.
2)Copy a number which the user has entered into a fixed cell (lets say cell F1).
3) I would like the macro to then paste the number in cell F1 into an equal amount of entries as column A

ie: if 10 entries in A the number is pasted into B 10 times.

thank you
Apr 4 '07 #1
Share this Question
Share on Google+
7 Replies


Expert 5K+
P: 8,434
Really simple query here. I want to write a macro that will work in this order:
...
Have you made any sort of start on this?
Apr 5 '07 #2

P: 22
This is what I have so far:


Expand|Select|Wrap|Line Numbers
  1. Sub Macro9()
  2. '
  3. ' Macro9 Macro
  4. ' Macro recorded on 04/04/2007
  5. '
  6.  
  7. '
  8.     Range("A2").Select
  9.     ActiveSheet.Paste
  10.     Range("E2:E26").Select
  11.     Selection.AutoFill Destination:=Range("E2:E424"), Type:=xlFillDefault
  12.     Range("E2:E424").Select
  13.     Selection.ClearContents
  14.     Range("D2:D349").Select
  15.     Selection.ClearContents
  16.     ActiveWindow.SmallScroll Down:=-18
  17.     Range("C1:C424").Select
  18.     ActiveWindow.SmallScroll Down:=-24
  19.     Range("A407").Select
  20.     ActiveWindow.SmallScroll Down:=-84
  21.     ActiveWindow.ScrollRow = 302
  22.     ActiveWindow.ScrollRow = 301
  23.     ActiveWindow.ScrollRow = 300
  24.     ActiveWindow.ScrollRow = 298
  25.     ActiveWindow.ScrollRow = 295
  26.     ActiveWindow.ScrollRow = 292
  27.     ActiveWindow.ScrollRow = 289
  28.     ActiveWindow.ScrollRow = 285
  29.     ActiveWindow.ScrollRow = 279
  30.     ActiveWindow.ScrollRow = 272
  31.     ActiveWindow.ScrollRow = 265
  32.     ActiveWindow.ScrollRow = 257
  33.     ActiveWindow.ScrollRow = 248
  34.     ActiveWindow.ScrollRow = 238
  35.     ActiveWindow.ScrollRow = 228
  36.     ActiveWindow.ScrollRow = 218
  37.     ActiveWindow.ScrollRow = 209
  38.     ActiveWindow.ScrollRow = 199
  39.     ActiveWindow.ScrollRow = 190
  40.     ActiveWindow.ScrollRow = 178
  41.     ActiveWindow.ScrollRow = 168
  42.     ActiveWindow.ScrollRow = 158
  43.     ActiveWindow.ScrollRow = 149
  44.     ActiveWindow.ScrollRow = 137
  45.     ActiveWindow.ScrollRow = 128
  46.     ActiveWindow.ScrollRow = 118
  47.     ActiveWindow.ScrollRow = 111
  48.     ActiveWindow.ScrollRow = 104
  49.     ActiveWindow.ScrollRow = 95
  50.     ActiveWindow.ScrollRow = 89
  51.     ActiveWindow.ScrollRow = 82
  52.     ActiveWindow.ScrollRow = 77
  53.     ActiveWindow.ScrollRow = 71
  54.     ActiveWindow.ScrollRow = 66
  55.     ActiveWindow.ScrollRow = 61
  56.     ActiveWindow.ScrollRow = 56
  57.     ActiveWindow.ScrollRow = 52
  58.     ActiveWindow.ScrollRow = 48
  59.     ActiveWindow.ScrollRow = 44
  60.     ActiveWindow.ScrollRow = 41
  61.     ActiveWindow.ScrollRow = 38
  62.     ActiveWindow.ScrollRow = 35
  63.     ActiveWindow.ScrollRow = 33
  64.     ActiveWindow.ScrollRow = 30
  65.     ActiveWindow.ScrollRow = 29
  66.     ActiveWindow.ScrollRow = 26
  67.     ActiveWindow.ScrollRow = 25
  68.     ActiveWindow.ScrollRow = 23
  69.     ActiveWindow.ScrollRow = 22
  70.     ActiveWindow.ScrollRow = 20
  71.     ActiveWindow.ScrollRow = 19
  72.     ActiveWindow.ScrollRow = 18
  73.     ActiveWindow.ScrollRow = 16
  74.     ActiveWindow.ScrollRow = 15
  75.     ActiveWindow.ScrollRow = 14
  76.     ActiveWindow.ScrollRow = 12
  77.     ActiveWindow.ScrollRow = 11
  78.     ActiveWindow.ScrollRow = 10
  79.     ActiveWindow.ScrollRow = 8
  80.     ActiveWindow.ScrollRow = 7
  81.     ActiveWindow.ScrollRow = 6
  82.     ActiveWindow.ScrollRow = 5
  83.     ActiveWindow.ScrollRow = 4
  84.     ActiveWindow.ScrollRow = 3
  85.     ActiveWindow.ScrollRow = 2
  86.     ActiveWindow.ScrollRow = 1
  87.     Range("G1:J1").Select
  88.     Selection.Copy
  89.     Range("A1").Select
  90.     ActiveSheet.Paste
  91.     Range("G4").Select
  92.     Application.CutCopyMode = False
  93.     Selection.Copy
  94.     Range("D2").Select
  95.     ActiveSheet.Paste
  96.     Range("E10").Select
  97.     Application.CutCopyMode = False
  98.     Cells.Select
  99.     Range("E10").Activate
  100.     Selection.Interior.ColorIndex = xlNone
  101.     Range("E8").Select
  102. End Sub
It is a recorded macro. G4 is the fixed cell I talked about.

thanks
Apr 6 '07 #3

SammyB
Expert 100+
P: 807
Hi,

Really simple query here. I want to write a macro that will work in this order:

1)Count the amount of cells in column A.
2)Copy a number which the user has entered into a fixed cell (lets say cell F1).
3) I would like the macro to then paste the number in cell F1 into an equal amount of entries as column A

ie: if 10 entries in A the number is pasted into B 10 times.

thank you
Why use a macro? Just use the Worksheet function CountIf. For example, your data is in column A, the user enters the number to count in D1, and the formula =COUNTIF(A:A,D1) in F1 gives the answer. 16 characters instead of 2,477 ;) --Sam
Apr 6 '07 #4

P: 22
Whoops!

Sorry, I've been really silly. I want to add the function TO the macro I've created. Not create a macro specifically for this purpose.
Apr 12 '07 #5

SammyB
Expert 100+
P: 807
Whoops!

Sorry, I've been really silly. I want to add the function TO the macro I've created. Not create a macro specifically for this purpose.
Application.WorksheetFunction gets you any of them:
Expand|Select|Wrap|Line Numbers
  1. MsgBox Application.WorksheetFunction.CountIf(Columns(1), Cells(1, 4))
Apr 16 '07 #6

Denburt
Expert 100+
P: 1,356
1)Count the amount of cells in column A.
2)Copy a number which the user has entered into a fixed cell (lets say cell F1).
in Cell F1 you can add the following
=COUNT(A:A)
or place it in your code
Range("F1") = "=Count(A:A)"

3) I would like the macro to then paste the number in cell F1 into an equal amount of entries as column A
Expand|Select|Wrap|Line Numbers
  1. Range("F1").Copy
  2. Range("B1", "b" & Range("F1").Value).Select
  3.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  4.         :=False, Transpose:=False
  5.  

All of the following code that is in your recorded macro can be disposed of as it is not providing anything essential. Unless you just like watching it scroll....

Expand|Select|Wrap|Line Numbers
  1.  ActiveWindow.SmallScroll Down:=-18
  2.     Range("C1:C424").Select
  3.     ActiveWindow.SmallScroll Down:=-24
  4.     Range("A407").Select
  5.     ActiveWindow.SmallScroll Down:=-84
  6.     ActiveWindow.ScrollRow = 302
  7.     ActiveWindow.ScrollRow = 301
  8.     ActiveWindow.ScrollRow = 300
  9.     ActiveWindow.ScrollRow = 298
  10.     ActiveWindow.ScrollRow = 295
  11.     ActiveWindow.ScrollRow = 292
  12.     ActiveWindow.ScrollRow = 289
  13.     ActiveWindow.ScrollRow = 285
  14.     ActiveWindow.ScrollRow = 279
  15.     ActiveWindow.ScrollRow = 272
  16.     ActiveWindow.ScrollRow = 265
  17.     ActiveWindow.ScrollRow = 257
  18.     ActiveWindow.ScrollRow = 248
  19.     ActiveWindow.ScrollRow = 238
  20.     ActiveWindow.ScrollRow = 228
  21.     ActiveWindow.ScrollRow = 218
  22.     ActiveWindow.ScrollRow = 209
  23.     ActiveWindow.ScrollRow = 199
  24.     ActiveWindow.ScrollRow = 190
  25.     ActiveWindow.ScrollRow = 178
  26.     ActiveWindow.ScrollRow = 168
  27.     ActiveWindow.ScrollRow = 158
  28.     ActiveWindow.ScrollRow = 149
  29.     ActiveWindow.ScrollRow = 137
  30.     ActiveWindow.ScrollRow = 128
  31.     ActiveWindow.ScrollRow = 118
  32.     ActiveWindow.ScrollRow = 111
  33.     ActiveWindow.ScrollRow = 104
  34.     ActiveWindow.ScrollRow = 95
  35.     ActiveWindow.ScrollRow = 89
  36.     ActiveWindow.ScrollRow = 82
  37.     ActiveWindow.ScrollRow = 77
  38.     ActiveWindow.ScrollRow = 71
  39.     ActiveWindow.ScrollRow = 66
  40.     ActiveWindow.ScrollRow = 61
  41.     ActiveWindow.ScrollRow = 56
  42.     ActiveWindow.ScrollRow = 52
  43.     ActiveWindow.ScrollRow = 48
  44.     ActiveWindow.ScrollRow = 44
  45.     ActiveWindow.ScrollRow = 41
  46.     ActiveWindow.ScrollRow = 38
  47.     ActiveWindow.ScrollRow = 35
  48.     ActiveWindow.ScrollRow = 33
  49.     ActiveWindow.ScrollRow = 30
  50.     ActiveWindow.ScrollRow = 29
  51.     ActiveWindow.ScrollRow = 26
  52.     ActiveWindow.ScrollRow = 25
  53.     ActiveWindow.ScrollRow = 23
  54.     ActiveWindow.ScrollRow = 22
  55.     ActiveWindow.ScrollRow = 20
  56.     ActiveWindow.ScrollRow = 19
  57.     ActiveWindow.ScrollRow = 18
  58.     ActiveWindow.ScrollRow = 16
  59.     ActiveWindow.ScrollRow = 15
  60.     ActiveWindow.ScrollRow = 14
  61.     ActiveWindow.ScrollRow = 12
  62.     ActiveWindow.ScrollRow = 11
  63.     ActiveWindow.ScrollRow = 10
  64.     ActiveWindow.ScrollRow = 8
  65.     ActiveWindow.ScrollRow = 7
  66.     ActiveWindow.ScrollRow = 6
  67.     ActiveWindow.ScrollRow = 5
  68.     ActiveWindow.ScrollRow = 4
  69.     ActiveWindow.ScrollRow = 3
  70.     ActiveWindow.ScrollRow = 2
  71.     ActiveWindow.ScrollRow = 1
  72.  
Apr 16 '07 #7

P: 1
Dear all expert programmers,

I always like to use this type of code to extract certain functions from Excel into the macro interface which has limited functions. It has worked well for me for quite a number of ocassions. But recently, it has been failing on me. May I know what's the reason. I had to resort to very funny means to resolve the problem but it works sometimes and it doesn't. The funny means that I was referring to is copy format from boxes that recognizes this code and pasting it to boxes that creates problem.
Range("B1").Value = "=CountA('New List'!A:A)"
Thank you so much. Take care. Have a nice day.
Jul 7 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.