Solved: Sorten Recorded Macro

[Nurofen]

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?

[VBA]
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[/VBA]

Thanking for your time

Nurofen

[Bob Phillips]

Not unless there is some pattern

[Nurofen]

Hi Xld,

No there is no pattern.

No problem

Thank you for your time

Nurofen

[jindon]

try

[vba]
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[/vba]

[Bob Phillips]

By the time he adds in 50, this will become a maintenance nightmare.

[jindon]

By the time he adds in 50, this will become a maintenance nightmare.

Who cares?

[Bob Phillips]

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]

OP is not asking about MAINTAINANCE, Shortnen the code, so I replied.

Have I done something wrong???

[Nurofen]

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]

You could use arrays to store the corresponding numbers
[vba]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

[/vba]

[Nurofen]

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

[Bob Phillips]

You could use arrays to store the corresponding numbers
[vba]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

[/vba]

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]

If column D entries are to be sequential then only one array is required.
[vba]
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

[/vba]

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.

[vba]
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
[/vba]

[Nurofen]

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]

Here's a udf solution, but it very much depends on your real data being consistent with your sample

[Nurofen]

Hi mdmackillop,

That is prefect, thank you so much





Thank you for your time

Nurofen

[Nurofen]

How do i run the macro?

Do i have to call the function?

Thank you again for the help mdmackillop


Nurofen

[mdmackillop]

You use this as any other Excel function, as shown in the table.