PDA

View Full Version : Solved: Sorten Recorded Macro



Nurofen
11-16-2007, 03:07 PM
Hi
I have recorded the macro below and removed Activecell and Select. I have about 50 line upto Range(D50) is there a another way it can be done?


sub cOUNT()
Sheets("Test").select
Range("D5").FormulaR1C1 = "='Data'!R[13]C[14]"
Range("D6").FormulaR1C1 = "='Data'!R[24]C[14]"
Range("D7").FormulaR1C1 = "='Data'!R[26]C[14]"
Range("D8").FormulaR1C1 = "='Data'!R[31]C[14]"
Range("D9").FormulaR1C1 = "='Data'!R[33]C[14]"
Range("D10").FormulaR1C1 = "='Data'!R[47]C[14]"

End Sub

Thanking for your time

Nurofen

xld
11-16-2007, 03:54 PM
Not unless there is some pattern

Nurofen
11-16-2007, 04:04 PM
Hi Xld,

No there is no pattern.

No problem

Thank you for your time

Nurofen :thumb

jindon
11-17-2007, 09:44 PM
try


sub cOUNT()
With Sheets("Test").Range("D5").Resize(6)
.FormulaR1C1 = [{"='Data'!R[13]C[14]";"='Data'!R[24]C[14]";"='Data'!R[26]C[14]";"='Data'!R[31]C[14]";"='Data'!R[33]C[14]";"='Data'!R[47]C[14]"}]
End With

End Sub

xld
11-18-2007, 03:07 AM
By the time he adds in 50, this will become a maintenance nightmare.

jindon
11-18-2007, 08:38 PM
By the time he adds in 50, this will become a maintenance nightmare.
Who cares?

xld
11-19-2007, 12:57 AM
Who cares?

With a stupid, arrogant attitude like that, I think you are showing why you come up with a less than helpful solution.

jindon
11-19-2007, 05:43 PM
OP is not asking about MAINTAINANCE, Shortnen the code, so I replied.

Have I done something wrong???

Nurofen
11-20-2007, 09:56 AM
Hi jindon,

I did ask to shorten, but as pointed out by Xld there is no pattern so using the recorded code and your code would make no difference.

Thank you for your input tho

Nurofen

mdmackillop
11-20-2007, 04:58 PM
You could use arrays to store the corresponding numbers
Sub Nurofen()
Dim arr
arr1 = Array(5, 6, 7, 8, 9, 10)
arr2 = Array(13, 24, 26, 31, 33, 47)

For i = 0 To UBound(arr1)
Sheets("Test").Select
Range("D" & arr1(i)).FormulaR1C1 = "='Data'!R[" & arr2(i) & "]C[14]"
Next

End Sub

Nurofen
11-20-2007, 05:08 PM
Hi mdmackillop,

I'm not sure how that will work only cause I don't understand. I will post a example tomorrow. If you don't mind to show me what you mean please.

Thank you for you time



Nurofen

xld
11-20-2007, 05:46 PM
You could use arrays to store the corresponding numbers
Sub Nurofen()
Dim arr
arr1 = Array(5, 6, 7, 8, 9, 10)
arr2 = Array(13, 24, 26, 31, 33, 47)

For i = 0 To UBound(arr1)
Sheets("Test").Select
Range("D" & arr1(i)).FormulaR1C1 = "='Data'!R[" & arr2(i) & "]C[14]"
Next

End Sub



I considered that but dismissed it because it offered no significant advantage, and would also be more dificult to maibtain that what he already had (not as difficult as jindon's offering, but still not as simple as what he had).

mdmackillop
11-21-2007, 05:56 AM
If column D entries are to be sequential then only one array is required.

Sub Nurofen()
Dim arr1
arr1 = Array(13, 24, 26, 31, 33, 47)
For i = 0 To UBound(arr1)
Sheets("Test").Select
Range("D" & i + 5).FormulaR1C1 = "='Data'!R[" & arr1(i) & "]C[14]"
Next
End Sub



Another consideration; are the cells between those selected on Data blank? ie, are you looking for the items in the same order, but with spaces removed? This looks for Constants and may need to be changed.


Sub Nurofen2()
Dim rng as range, r as range, i as long
Set rng = Sheets("Data").Columns("R:R").SpecialCells(xlCellTypeConstants)
For Each r In rng
Sheets("Test").Range("D" & i + 5).Formula = "='Data'!" & r.Address
i = i + 1
Next
End Sub

Nurofen
11-21-2007, 12:54 PM
Hi mdmackillop,

I have attached a example for a better understanding. I'm looking to send the values of the cells in black font over to the other sheet the red fonts will not be visible to the user.

I can now see how your method works.

Thanks for your time



Nurofen

mdmackillop
11-21-2007, 02:48 PM
Here's a udf solution, but it very much depends on your real data being consistent with your sample

Nurofen
11-21-2007, 03:39 PM
Hi mdmackillop,

That is prefect, thank you so much



:thumb

Thank you for your time

Nurofen :beerchug:

Nurofen
11-21-2007, 03:43 PM
How do i run the macro?

Do i have to call the function?

Thank you again for the help mdmackillop


Nurofen

mdmackillop
11-21-2007, 03:46 PM
You use this as any other Excel function, as shown in the table.