Solved

# Copy data from one cell into single cells

Posted on 2012-08-30
Medium Priority
367 Views
Last Modified: 2012-08-30
Hello Experts,

I have a macro kindly written by byundt which searches for certain data and copies it to another worksheet.  For some reason the macro will not copy the data from the last part of the data from the cell.

I have attached an example of the raw data and the macro I use to extract the relevant data.

I hope you can help.

Thanks,
Infosec36
Data.xls
0
Question by:Sonia Bowditch
2 Comments

LVL 18

Accepted Solution

krishnakrkc earned 2000 total points
ID: 38349850
Hi

Try this

``````Sub kTest()

Dim ka, k(), i As Long, H, m As Long
Dim dic As Object, x, y, j As Long

ka = Worksheets("Raw Data").Range("a1").CurrentRegion.Value2

H = Worksheets("Format").Range("a1").CurrentRegion.Rows(1).Value2

ReDim k(1 To UBound(ka, 1), 1 To UBound(H, 2))

Set dic = CreateObject("scripting.dictionary")
dic.comparemode = 1

For i = 1 To UBound(H, 2)
dic.Item(Trim\$(H(1, i))) = i
Next

For i = 1 To UBound(ka, 1)
x = Split(ka(i, 1), vbLf)
For m = 0 To UBound(x)
If InStr(1, x(m), ":") Then
y = Split(x(m), ":")
If dic.exists(Trim\$(y(0)) & ":") Then
k(i, dic.Item(Trim\$(y(0)) & ":")) = y(1)
End If
End If
Next
Next

Worksheets("Format").Range("a2").Resize(UBound(k, 1), dic.Count) = k

End Sub
``````

Kris
0

Author Closing Comment

ID: 38349904
Works perfectly, thank you.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
###### Suggested Courses
Course of the Month15 days, 5 hours left to enroll

#### 840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.