Help with Marco

[crag8119]

Hi,

I want my macro to populate column 'N30:N30000' with my formula.

However, for some reason it won't compile the macro due to ' Syntax error'. Any ideas how to resolve this:

Sub FillDown() Dim strFormulas(1) As Variant


With ThisWorkbook.Sheets("Sheet1")
strFormulas(1) = "=IF(ISNUMBER(MATCH("*|"&LEFT(D5,8),$E$1:$E$100,0)),LEFT(D5,8),"No match")"


.Range("N30").Formula = strFormulas
.Range("N30:N30000").FillDown
End With


End Sub

 

[vcoolio]

Hello Crag8119,

Try it slightly amended as follows:-

Sub FillDown()

With ThisWorkbook.Sheets("Sheet1")
       .Range("N30").Formula = "=IF(ISNUMBER(MATCH([COLOR=#ff0000]""*|""[/COLOR]&LEFT(D5,8),$E$1:$E$100,0)),LEFT(D5,8),[COLOR=#ff0000]""No match""[/COLOR])"
       .Range("N30:N30000").FillDown
End With

End Sub

or

Sub Test()

Sheet1.Range("N30:N30000").Formula = "=IF(ISNUMBER(MATCH(""*|""&LEFT(D5,8),$E$1:$E$100,0)),LEFT(D5,8),""No match"")"

End Sub

I hope that this helps.

Cheerio,
vcoolio.

 

[crag8119]

Hello Crag8119,

Try it slightly amended as follows:-

Sub FillDown()

With ThisWorkbook.Sheets("Sheet1")
       .Range("N30").Formula = "=IF(ISNUMBER(MATCH([COLOR=#ff0000]""*|""[/COLOR]&LEFT(D5,8),$E$1:$E$100,0)),LEFT(D5,8),[COLOR=#ff0000]""No match""[/COLOR])"
       .Range("N30:N30000").FillDown
End With

End Sub

or

Sub Test()

Sheet1.Range("N30:N30000").Formula = "=IF(ISNUMBER(MATCH(""*|""&LEFT(D5,8),$E$1:$E$100,0)),LEFT(D5,8),""No match"")"

End Sub

I hope that this helps.

Cheerio,
vcoolio.

Brilliant thank you

 

[vcoolio]

You're welcome Crag. I'm glad to have been able to help.

Cheerio,
vcoolio.