Parse data

[kckay]

I am trying to copy and reformat data from a master Excel worksheet (generated by a report generator) to detail sheets.

The report generator merges cells to format the data on the screen. This creates a variable length field (variable number of cells merged).

How would I unmerge the cells in VBA?

The cells I would need to reference to extract the data to move to the detail worksheets are not the same. I am thinking that I would walk along the cells and check for the next cell with data. Something along the lines of:
loop
If cell <> "" then
move cell to target_cell
end if
end loop

Also, within a single cell, I may need to extract some of the data to move to separate target cells: CO00159070 - Kingdom Park. CO00159070 needs to go to target_cell1 and Kingdom Park needs to go to target_cell2. How do I parse the data?

Thank you.

 

[VoG]

This should help you with unmerging http://www.mrexcel.com/forum/showthread.php?t=335123

 

[vba.adam]

Try using the instr function to find the hyphen in the string you want to parse. Then you can use the len, left and right string functions to break it up.

Private Sub test()
Dim x As String, firstPart as string, secondPart as string
x = "CO00159070 - Kingdom Park"
firstPart = Left(x, InStr(x, "-") - 2)
secondPart = Right(x, Len(x) - InStr(x, "-") - 1)
End Sub

P.S. if the amount of spaces around the character you are searching on is variable, you might want to use trim, instead of subtracting for the spaces.

 

[kckay]

Thank you, will continue working with this.

Now, I am feeling like a dolt and cannot get the parsed data to the target cell without a 1004 error:

Worksheets("System Overview").Range("B6") = PWSID

Thank you.