crag8119

New Member
Joined
Mar 25, 2014
Messages
38
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
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,001
Hello Crag8119,

Try it slightly amended as follows:-

Code:
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

Code:
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

New Member
Joined
Mar 25, 2014
Messages
38
Hello Crag8119,

Try it slightly amended as follows:-

Code:
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

Code:
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

Well-known Member
Joined
Jun 29, 2014
Messages
1,001
You're welcome Crag. I'm glad to have been able to help.

Cheerio,
vcoolio.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,612
Messages
5,523,896
Members
409,542
Latest member
Shezz01

This Week's Hot Topics

Top