remove " and concatenate on cell colour and run RS232IP2

[sydinstaller]

Hi,
This is a tricky one to try explain, but here we go...

Usefully background info:
Following on from this post: http://www.mrexcel.com/forum/showthread.php?t=491155&highlight=RS232IP

My sheets have become much more complex and repetitive than I ever expected.

What I would like to do is:
Copy the data from between the "" in column A (Column F shows example results)
Take the data from the BLUE cell in column A and add it to the cells below it (Column G shows example results) Then at the next BLUE cell start the cycle again.

There are some GREY cells. These are not implemented so they can be ignored but it is OK if they are processed.

I need this to repeat down the entire column.

Then if possible (This is not required as I can run a separate command manually) I would like to run the RS232IP2 script in column H.

Thanks in advance.
Daniel

Excel Workbook
	A	B	C	D	E	F	G	H
1	"RES" - Monitor Out Resolution					RES
2	"00"	sets Through	Yes	Yes		00	!1RES00	49 53 43 50 00 00 00 10 00 00 00 08 01 00 00 00 21 31 52 45 53 30 30
3	"01"	sets Auto(HDMI Output Only)	Yes	Yes		01	!1RES01	49 53 43 50 00 00 00 10 00 00 00 08 01 00 00 00 21 31 52 45 53 30 31
4	"02"	sets 480p	Yes	Yes		02	!1RES02	49 53 43 50 00 00 00 10 00 00 00 08 01 00 00 00 21 31 52 45 53 30 32
5	"03"	sets 720p	Yes	Yes		03	!1RES03	49 53 43 50 00 00 00 10 00 00 00 08 01 00 00 00 21 31 52 45 53 30 33
6	"UP"	sets Monitor Out Resolution Wrap-Around Up	Yes	Yes		UP	!1RESUP	49 53 43 50 00 00 00 10 00 00 00 08 01 00 00 00 21 31 52 45 53 55 50
7	"QSTN"	gets The Monitor Out Resolution	Yes	Yes		QSTN	!1RESQSTN	49 53 43 50 00 00 00 10 00 00 00 0A 01 00 00 00 21 31 52 45 53 51 53 54 4E
8	"ISF" - ISF Mode					ISF
9	"00"	sets ISF Mode Custom	No	No		00	!1ISF00	49 53 43 50 00 00 00 10 00 00 00 08 01 00 00 00 21 31 49 53 46 30 30
10	"01"	sets ISF Mode Day	No	No		01	!1ISF01	49 53 43 50 00 00 00 10 00 00 00 08 01 00 00 00 21 31 49 53 46 30 31
11	"VWM" - Video Wide Mode					VWM
12	"00"	sets Auto	Yes	Yes		00	!1VWM00	49 53 43 50 00 00 00 10 00 00 00 08 01 00 00 00 21 31 56 57 4D 30 30
13	"01"	sets 4:3	Yes	Yes		01	!1VWM01	49 53 43 50 00 00 00 10 00 00 00 08 01 00 00 00 21 31 56 57 4D 30 31
14	"05"	sets Smart Zoom	No	No		05	!1VWM05	49 53 43 50 00 00 00 10 00 00 00 08 01 00 00 00 21 31 56 57 4D 30 35
15	"UP"	sets Video Zoom Mode Wrap-Around Up	Yes	Yes		UP	!1VWMUP	49 53 43 50 00 00 00 10 00 00 00 08 01 00 00 00 21 31 56 57 4D 55 50
16	"QSTN"	gets Video Zoom Mode	Yes	Yes		QSTN	!1VWMQSTN	49 53 43 50 00 00 00 10 00 00 00 0A 01 00 00 00 21 31 56 57 4D 51 53 54 4E
17	"VPM" -Video Picture Mode					VPM
18	"00"	sets Through	Yes	Yes		00	!1VPM00	49 53 43 50 00 00 00 10 00 00 00 08 01 00 00 00 21 31 56 50 4D 30 30
19	"01"	sets Custom	Yes	Yes		01	!1VPM01	49 53 43 50 00 00 00 10 00 00 00 08 01 00 00 00 21 31 56 50 4D 30 31
20	"02"	sets Cinema	Yes	Yes		02	!1VPM02	49 53 43 50 00 00 00 10 00 00 00 08 01 00 00 00 21 31 56 50 4D 30 32
Sheet1

Here is the code for RS232IP2

Function RS232IP2(s As String, Optional delim As String = " ") As String ' The usage would be RS232IP2(cell to change, OPTIONAL delimiter as cell referance)

Dim byt() As Byte
Dim j As Long
 
Const HEADER As String = "49 53 43 50 00 00 00 10 00 00 00 ## 01 00 00 00" ' This is the HEADDER. ## is the section where LEN calculated and is placed

 
RS232IP2 = Replace(Replace(HEADER, "##", Right("0" & Hex(Len(s) + 1), 2)), " ", delim)
byt = StrConv(s, vbFromUnicode)
For j = 0 To Len(s) - 1
    RS232IP2 = RS232IP2 & delim & Hex(byt(j))
Next j
End Function


' Thank you to PGC01 and sandeep.warrier from the MREXCEL forums for providing this script.
' http://www.mrexcel.com/forum/showthread.php?t=491155&highlight=RS232IP


' Original code before I messed with it :)

' Function RS232IP(s As String, HEADER As String, Optional delim As String = " ") As String
'    Dim j As Long
'
'    RS232IP = Replace(Replace(HEADER, "##", Right("0" & Hex(Len(s) + 1), 2)), " ", delim)
'
'    For j = 1 To Len(s)
'        RS232IP = RS232IP & delim & Hex(Asc(Mid(s, j, 1)))
'    Next j
'End Function

 

[ZVI]

Happy to help, Daniel !