Need Help with a spread sheet.

Brian12q34

New Member
Joined
Oct 14, 2016
Messages
9
I have a spreadsheet that has 7 columns of URLs and i need to place a double hash tag between each URL row a is my mpn row b is my image name row c is the first set of URLs row d is for the (2) ## hash tags row e is my second set of URLs row f is for 2 hashtags row g is my third set of URLs row H is 2 Hashtags Row I is my 4th set of URLs row J is for 2 hash tags row k is my 5th set of URLs Row L is for 2 hash tags row m is two hash tags row n is my last set of URLs. I am looking for a command to tell excel that if their is a URL in the Row to the right to place 2 hash tags next to it . see example i have 115607 rows. Thank you Howard

[TABLE="width: 3951"]
<colgroup><col span="2"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Item #[/TD]
[TD]Image name[/TD]
[TD]URL to large image[/TD]
[TD]##[/TD]
[TD]Extra Img 1[/TD]
[TD]##[/TD]
[TD]Extra Img 2[/TD]
[TD]##[/TD]
[TD]Extra Img 3[/TD]
[TD]##[/TD]
[TD]Extra Img 4[/TD]
[TD]##[/TD]
[TD]Extra Img 5[/TD]
[TD]##[/TD]
[TD]Extra Img 6[/TD]
[/TR]
[TR]
[TD="align: right"]2758[/TD]
[TD]2758.jpg[/TD]
[TD]http://www.imageserverhost.com/simg/C122/large/2758.jpg[/TD]
[TD]##[/TD]
[TD]http://www.imageserverhost.com/simg/C122/original/2758_26007992.jpg[/TD]
[TD]##[/TD]
[TD]http://www.imageserverhost.com/simg/C122/original/2758_26007993.jpg[/TD]
[TD]##[/TD]
[TD]http://www.imageserverhost.com/simg/C122/original/2758_26007994.jpg[/TD]
[TD]##[/TD]
[TD]http://www.imageserverhost.com/simg/C122/original/2758_26007995.jpg[/TD]
[TD]##[/TD]
[TD]http://www.imageserverhost.com/simg/C122/original/2758_26007996.jpg[/TD]
[TD]##[/TD]
[TD]http://www.imageserverhost.com/simg/C122/original/2758_26007997.jpg[/TD]
[/TR]
[TR]
[TD="align: right"]5349[/TD]
[TD]5349.jpg[/TD]
[TD]http://www.imageserverhost.com/simg/C122/large/5349.jpg[/TD]
[TD]##[/TD]
[TD]http://www.imageserverhost.com/simg/C122/original/5349_25904230.jpg[/TD]
[TD]##[/TD]
[TD]http://www.imageserverhost.com/simg/C122/original/5349_25904231.jpg[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5659[/TD]
[TD]5659.jpg[/TD]
[TD]http://www.imageserverhost.com/simg/C122/large/5659.jpg[/TD]
[TD]##[/TD]
[TD]http://www.imageserverhost.com/simg/C122/original/5659_25696155.jpg[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6652[/TD]
[TD]6652.jpg[/TD]
[TD]http://www.imageserverhost.com/simg/C122/large/6652.jpg[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6679[/TD]
[TD]6679.jpg[/TD]
[TD]http://www.imageserverhost.com/simg/C122/large/6679.jpg[/TD]
[TD]##[/TD]
[TD]http://www.imageserverhost.com/simg/C122/original/6679_26339424.jpg[/TD]
[TD]##[/TD]
[TD]http://www.imageserverhost.com/simg/C122/original/6679_26339425.jpg[/TD]
[TD]##[/TD]
[TD]http://www.imageserverhost.com/simg/C122/original/6679_26339426.jpg[/TD]
[TD]##[/TD]
[TD]http://www.imageserverhost.com/simg/C122/original/6679_26339427.jpg[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7104[/TD]
[TD]7104.jpg[/TD]
[TD]http://www.imageserverhost.com/simg/C122/large/7104.jpg[/TD]
[TD]##[/TD]
[TD]http://www.imageserverhost.com/simg/C122/original/7104_25711439.jpg[/TD]
[TD]##[/TD]
[TD]http://www.imageserverhost.com/simg/C122/original/7104_25711440.jpg[/TD]
[TD]##[/TD]
[TD]http://www.imageserverhost.com/simg/C122/original/7104_25711441.jpg[/TD]
[TD]##[/TD]
[TD]http://www.imageserverhost.com/simg/C122/original/7104_25711442.jpg[/TD]
[TD]##[/TD]
[TD]http://www.imageserverhost.com/simg/C122/original/7104_25711443.jpg[/TD]
[TD]##[/TD]
[TD]http://www.imageserverhost.com/simg/C122/original/7104_25711444.jpg[/TD]
[/TR]
[TR]
[TD="align: right"]7105[/TD]
[TD]7105.jpg[/TD]
[TD]http://www.imageserverhost.com/simg/C122/large/7105.jpg[/TD]
[TD]##[/TD]
[TD]http://www.imageserverhost.com/simg/C122/original/7105_25602039.jpg[/TD]
[TD]##[/TD]
[TD]http://www.imageserverhost.com/simg/C122/original/7105_25602040.jpg[/TD]
[TD]##[/TD]
[TD]http://www.imageserverhost.com/simg/C122/original/7105_25602041.jpg[/TD]
[TD]##[/TD]
[TD]http://www.imageserverhost.com/simg/C122/original/7105_25769868.jpg[/TD]
[TD]##[/TD]
[TD]http://www.imageserverhost.com/simg/C122/original/7105_25769869.jpg[/TD]
[TD]##[/TD]
[TD]http://www.imageserverhost.com/simg/C122/original/7105_25769870.jpg[/TD]
[/TR]
[TR]
[TD="align: right"]7157[/TD]
[TD]7157.jpg[/TD]
[TD]http://www.imageserverhost.com/simg/C122/large/7157.jpg[/TD]
[TD]##[/TD]
[TD]http://www.imageserverhost.com/simg/C122/original/7157_25715963.jpg[/TD]
[TD]##[/TD]
[TD]http://www.imageserverhost.com/simg/C122/original/7157_25715964.jpg[/TD]
[TD]##[/TD]
[TD]http://www.imageserverhost.com/simg/C122/original/7157_25715965.jpg[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7340[/TD]
[TD]7340.jpg[/TD]
[TD]http://www.imageserverhost.com/simg/C122/large/7340.jpg[/TD]
[TD]##[/TD]
[TD]http://www.imageserverhost.com/simg/C122/original/7340_25712591.jpg[/TD]
[TD]##[/TD]
[TD]http://www.imageserverhost.com/simg/C122/original/7340_25712592.jpg[/TD]
[TD]##[/TD]
[TD]http://www.imageserverhost.com/simg/C122/original/7340_25712593.jpg[/TD]
[TD]##[/TD]
[TD]http://www.imageserverhost.com/simg/C122/original/7340_25712594.jpg[/TD]
[TD]##[/TD]
[TD]http://www.imageserverhost.com/simg/C122/original/7340_25712595.jpg[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Firstly, "D","F","H","J","L","N" are columns, not rows !
Secondly are the columns for the hashtags already there and ready, or d they need inserting ?
Are the columns the same length ?
 
Upvote 0
Maybe this macro then

Code:
Sub MM1()
Dim lr As Long, c As Long
lr = Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
    For c = 4 To 14 Step 2
        Range(Cells(1, c), Cells(lr, c)).Value = "##"
    Next c
End Sub
 
Upvote 0
glad to help....:beerchug:
 
Last edited:
Upvote 0
I tried the code you sent me today and i am getting an error "can not run Visual Basic becasue of a syntax error
 
Upvote 0
Sub MM1()
Dim lr As Long, c As Long
lr = Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
For c = 4 To 14 Step 2
Range(Cells(1, c), Cells(lr, c)).Value = "##"
Next c
End Sub
Sub MM1()
Dim lr As Long, c As Long
lr = Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
For c = 4 To 14 Step 2
Range(Cells(1, c), Cells(lr, c)).Value = "##"
Next c
ActiveWindow.SmallScroll Down:=27
Range( _
"D1,H23312:H23328,J23312:J23328,J23330:J23335,J23337:J23339,J23342:J23349,J23351:J23355,H23330:H23356,N23315:N23316,N23318:N23323,N23325:N23326,N23328,N23330,N23332:N23333,N23337:N23339,N23343,N23345:N23348,N23352:N23353,N23355" _
).Select
Range("N23355").Activate
ActiveWindow.SmallScroll Down:=27
Union(Range( _
"H23381:H23383,J23381:J23382,L23381:L23382,N23381:N23382,D1,H23312:H23328,J23312:J23328,J23330:J23335,J23337:J23339,J23342:J23349,J23351:J23355,H23330:H23356,N23315:N23316,N23318:N23323,N23325:N23326,N23328,N23330,N23332:N23333,N23337:N23339,N23343" _
), Range("N23367:N23378,J23359:J23361,J23363:J23378,H23358:H23378")).Select
Range("N23381").Activate
Application.CutCopyMode = False
Application.WindowState = xlNormal
ActiveWindow.SmallScroll Down:=57
Union(Range( _
"H23381:H23383,J23381:J23382,L23381:L23382,N23381:N23382,H23385:H23387,J23385:J23387,L23385:L23387,N23385,N23387,N23389:N23391,N23393:N23394,N23396:N23397,N23403:N23404,N23409:N23412,N23419:N23420,N23422:N23423,N23425:N23426,N23429:N23430,N23433:N23434" _
), Range( _
"J23432:J23435,J23437:J23439,H23389:H23399,H23401:H23440,D1,H23312:H23328,J23312:J23328,J23330:J23335,J23337:J23339,J23342:J23349,J23351:J23355,H23330:H23356,N23315:N23316,N23318:N23323,N23325:N23326,N23328,N23330,N23332:N23333,N23337:N23339,N23343" _
), Range("N23367:N23378,J23359:J23361,J23363:J23378,H23358:H23378")).Select
Range("H23401").Activate
ActiveWindow.SmallScroll Down:=57
Union(Range( _
"H23381:H23383,J23381:J23382,L23381:L23382,N23381:N23382,H23385:H23387,J23385:J23387,L23385:L23387,N23385,N23387,N23389:N23391,N23393:N23394,N23396:N23397,N23403:N23404,N23409:N23412,N23419:N23420,N23422:N23423,N23425:N23426,N23429:N23430,N23433:N23434" _
), Range( _
"J23432:J23435,J23437:J23439,H23389:H23399,H23401:H23440,H23442:H23453,H23455:H23470,H23472:H23504,H23508:H23513,J23508:J23511,L23509:L23511,N23509:N23511,N23501:N23505,N23500,N23497:N23498,N23493,N23486:N23490,N23478:N23484,N23473:N23475,N23470,N23463" _
), Range( _
"L23486:L23495,L23497:L23498,L23500:L23505,J23497:J23505,J23486:J23495,J23477:J23484,J23473:J23475,J23469:J23470,J23467,J23456:J23463,J23442:J23453,D1,H23312:H23328,J23312:J23328,J23330:J23335,J23337:J23339,J23342:J23349,J23351:J23355,H23330:H23356" _
), Range( _
"L23343,L23345:L23349,L23351:L23355,L23359:L23360,L23364:L23365,L23367:L23378,N23360,N23367:N23378,J23359:J23361,J23363:J23378,H23358:H23378" _
)).Select
Range("J23442").Activate
ActiveWindow.SmallScroll Down:=75
Union(Range( _
"H23381:H23383,J23381:J23382,L23381:L23382,N23381:N23382,H23385:H23387,J23385:J23387,L23385:L23387,N23385,N23387,N23389:N23391,N23393:N23394,N23396:N23397,N23403:N23404,N23409:N23412,N23419:N23420,N23422:N23423,N23425:N23426,N23429:N23430,N23433:N23434" _
), Range( _
"J23432:J23435,J23437:J23439,H23389:H23399,H23401:H23440,H23442:H23453,H23455:H23470,H23472:H23504,H23508:H23513,J23508:J23511,L23509:L23511,N23509:N23511,N23501:N23505,N23500,N23497:N23498,N23493,N23486:N23490,N23478:N23484,N23473:N23475,N23470,N23463" _
), Range( _
"L23486:L23495,L23497:L23498,L23500:L23505,J23497:J23505,J23486:J23495,J23477:J23484,J23473:J23475,J23469:J23470,J23467,J23456:J23463,J23442:J23453,H23515:H23527,J23516:J23527,L23516:L23518,L23520:L23527,N23516:N23518,N23521:N23527,N23529,N23531:N23532" _
), Range( _
"L23556:L23557,L23564,L23568:L23572,L23575:L23583,L23574,L23585,L23588,D1,H23312:H23328,J23312:J23328,J23330:J23335,J23337:J23339,J23342:J23349,J23351:J23355,H23330:H23356,N23315:N23316,N23318:N23323,N23325:N23326,N23328,N23330,N23332:N23333,N23337:N23339" _
), Range( _
"L23364:L23365,L23367:L23378,N23360,N23367:N23378,J23359:J23361,J23363:J23378,H23358:H23378" _
)).Select
Range("L23588").Activate
ActiveWorkbook.Save
Range("F1").Select
Application.CutCopyMode = False
End Sub
 
Upvote 0
I am looking for a command to tell excel that if their is a URL in the Row to the right to place 2 hash tags next to it.

you could try a nested if statement in P2 and drag it down to your last row

Code:
=IF(O2<>"",C2&D2&E2&F2&G2&H2&I2&J2&K2&L2&M2&N2&O2,IF(M2<>"",C2&D2&E2&F2&G2&H2&I2&J2&K2&L2&M2,IF(K2<>"",C2&D2&E2&F2&G2&H2&I2&J2&K2,IF(I2<>"",C2&D2&E2&F2&G2&H2&I2,IF(G2<>"",C2&D2&E2&F2&G2,IF(E2<>"",C2&D2&E2,IF(C2<>"",C2,"Blank")))))))

and here would be the VBA to automatically do that

Code:
Sub ifSTATEMENT()
Dim lastRow As Long

    Range("P2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]<>"""",RC[-13]&RC[-12]&RC[-11]&RC[-10]&RC[-9]&RC[-8]&RC[-7]&RC[-6]&RC[-5]&RC[-4]&RC[-3]&RC[-2]&RC[-1],IF(RC[-3]<>"""",RC[-13]&RC[-12]&RC[-11]&RC[-10]&RC[-9]&RC[-8]&RC[-7]&RC[-6]&RC[-5]&RC[-4]&RC[-3],IF(RC[-5]<>"""",RC[-13]&RC[-12]&RC[-11]&RC[-10]&RC[-9]&RC[-8]&RC[-7]&RC[-6]&RC[-5],IF(RC[-7]<>"""",RC[-13]&RC[-12]&RC[-11]&RC[-10]&RC[-9]&RC[-8]&RC[-7],IF(RC[-" & _
        "9]<>"""",RC[-13]&RC[-12]&RC[-11]&RC[-10]&RC[-9],IF(RC[-11]<>"""",RC[-13]&RC[-12]&RC[-11],IF(RC[-13]<>"""",RC[-13],""Blank"")))))))"
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("P2").AutoFill Destination:=Range("P2:P" & lastRow)
    Columns("P:P").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top