Need Help with a spread sheet.

B

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

Item #Image nameURL to large image##Extra Img 1##Extra Img 2##Extra Img 3##Extra Img 4##Extra Img 5##Extra Img 6
27582758.jpghttp://www.imageserverhost.com/simg/C122/large/2758.jpg##http://www.imageserverhost.com/simg/C122/original/2758_26007992.jpg##http://www.imageserverhost.com/simg/C122/original/2758_26007993.jpg##http://www.imageserverhost.com/simg/C122/original/2758_26007994.jpg##http://www.imageserverhost.com/simg/C122/original/2758_26007995.jpg##http://www.imageserverhost.com/simg/C122/original/2758_26007996.jpg##http://www.imageserverhost.com/simg/C122/original/2758_26007997.jpg
53495349.jpghttp://www.imageserverhost.com/simg/C122/large/5349.jpg##http://www.imageserverhost.com/simg/C122/original/5349_25904230.jpg##http://www.imageserverhost.com/simg/C122/original/5349_25904231.jpg
56595659.jpghttp://www.imageserverhost.com/simg/C122/large/5659.jpg##http://www.imageserverhost.com/simg/C122/original/5659_25696155.jpg
66526652.jpghttp://www.imageserverhost.com/simg/C122/large/6652.jpg
66796679.jpghttp://www.imageserverhost.com/simg/C122/large/6679.jpg##http://www.imageserverhost.com/simg/C122/original/6679_26339424.jpg##http://www.imageserverhost.com/simg/C122/original/6679_26339425.jpg##http://www.imageserverhost.com/simg/C122/original/6679_26339426.jpg##http://www.imageserverhost.com/simg/C122/original/6679_26339427.jpg
71047104.jpghttp://www.imageserverhost.com/simg/C122/large/7104.jpg##http://www.imageserverhost.com/simg/C122/original/7104_25711439.jpg##http://www.imageserverhost.com/simg/C122/original/7104_25711440.jpg##http://www.imageserverhost.com/simg/C122/original/7104_25711441.jpg##http://www.imageserverhost.com/simg/C122/original/7104_25711442.jpg##http://www.imageserverhost.com/simg/C122/original/7104_25711443.jpg##http://www.imageserverhost.com/simg/C122/original/7104_25711444.jpg
71057105.jpghttp://www.imageserverhost.com/simg/C122/large/7105.jpg##http://www.imageserverhost.com/simg/C122/original/7105_25602039.jpg##http://www.imageserverhost.com/simg/C122/original/7105_25602040.jpg##http://www.imageserverhost.com/simg/C122/original/7105_25602041.jpg##http://www.imageserverhost.com/simg/C122/original/7105_25769868.jpg##http://www.imageserverhost.com/simg/C122/original/7105_25769869.jpg##http://www.imageserverhost.com/simg/C122/original/7105_25769870.jpg
71577157.jpghttp://www.imageserverhost.com/simg/C122/large/7157.jpg##http://www.imageserverhost.com/simg/C122/original/7157_25715963.jpg##http://www.imageserverhost.com/simg/C122/original/7157_25715964.jpg##http://www.imageserverhost.com/simg/C122/original/7157_25715965.jpg
73407340.jpghttp://www.imageserverhost.com/simg/C122/large/7340.jpg##http://www.imageserverhost.com/simg/C122/original/7340_25712591.jpg##http://www.imageserverhost.com/simg/C122/original/7340_25712592.jpg##http://www.imageserverhost.com/simg/C122/original/7340_25712593.jpg##http://www.imageserverhost.com/simg/C122/original/7340_25712594.jpg##http://www.imageserverhost.com/simg/C122/original/7340_25712595.jpg

<colgroup><col span="2"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
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....(y)
 
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
Brian12q34 said:
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.
Click to expand...

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
You must log in or register to reply here.

Similar threads

M
  • Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
M
  • Question
VBA to import various ranges of cell information on multiple worksheets and import into new workbook
2
Replies
17
Views
5K
metrokurt
M
A
  • Question
Two Lists of Contacts within one sheet. Need help sorting to find duplicates/format
2
Replies
10
Views
2K
Atlanta
A
D
  • Question
Need help with barcode scanner excel sheet (VBA)
Replies
0
Views
2K
drowe005
D
InaCell
  • Question
Relationship to individuals (1)
Replies
0
Views
770
InaCell
InaCell

Forum statistics

Threads
1,217,371
Messages
6,136,168
Members
449,996
Latest member
duraichandra

Share this page

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