Convert numbers to relate to a folder/file name

[sam71]

Hi all,

Need some help putting this formular into vba code to get the result as shown in AD & AE! The amount of rows changes on a daily basis so it would need to relate to where the data finishes...

=CONCATENATE("images/supplier1/",TEXT(INT(AC2/1000),"00000"),"/",CONCATENATE(AC2,".jpg"))

 

[Smitty]

Welcome to the Board!

This should get you started:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>        <br>        i = Cells(Rows.Count, "AC").End(xlUp).Row<br>        <br>        Range("AD2:AD" & i).Formula = "=""images/supplier1/""&TEXT(INT(AC2/1000),""00000"")&""/""&AC2&"".jpg"""<br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

HTH,

 

[sam71]

Thanks Smitty it works a treat!

However how would I get that to run again in the next column? In this case for a web address!

I changed the code around to reflect the new column but it says there is a duplicate entry (or something similar)

 Dim i As Long
 
        i = Cells(Rows.Count, "AC").End(xlUp).Row
 
        Range("AE2:AE" & i).Formula = "=""http://misc.supplier1.com.au/images/products/""&TEXT(INT(AC2/1000),""00000"")&""/""&AC2&"".jpg"""

 

[sam71]

I removed the http:// and it worked!

Looks to be all good at this stage

 

[Smitty]

However how would I get that to run again in the next column? In this case for a web address!

I'd repeat the first line of code that inputs the formula, just adjust the range and the formula to suit.

Range("AD2:AD" & i).Formula = "=""images/supplier1/""&TEXT(INT(AC2/1000),""00000"")&""/""&AC2&"".jpg"""
Range("AE2:AE" & i).Formula = "=Second Formula"

 

[sam71]

I removed the second entry

i = Cells(Rows.Count, "AC").End(xlUp).Row

and it still works!

I am very new to doing this and any help is greatly appreciated...

Thanks again

 

[Smitty]

OK,

All the i does is identify the last row in column AC, which is your data row. Then we tell the code to copy from AD2 to the last row in AC, which is the "AD2:AD" & i part. We're still staying in column AD, but using column AC to help us do the work.

So the i will work for AE as well, since that number isn't changing. But you need the two separate formula lines of code since you have two different formulas.

 

[sam71]

I am at the next step and have come across another hurdle!

Because there isn't an image number for every product (row) I end up with

<TABLE style="WIDTH: 284pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=378><COLGROUP><COL style="WIDTH: 284pt; mso-width-source: userset; mso-width-alt: 13824" width=378><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 284pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 width=378>misc.supplier1.com.au/images/products/00000/.jpg


How would I go about removing all of these entries? I tried doing a Replace (with a blank) manually but I get an error, I assume it is because of the formula

I also tried to add the following but nothing happens

Columns("AE:AE").Select
    Selection.Replace What:="misc.supplier1.com.au/images/products/00000/.jpg", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

</TD></TR></TBODY></TABLE>

 

[sam71]

I created another macro which ran a saved version of the modified file and it works