Convert XML to VBA: Copy-lookup-paste problem

[McMuffin]

Hi all

I’ve inherited an old excel file from a colleague that I need to modify.

My problem is that the file contains old XML-code and I need to convert this to VBA and I know next to nothing about neither XML nor VBA!

I have one sheet (Sheet1) with data that changes each month. In cell B5, sheet1, it’s stated which month the data in the sheet is referring to. On sheet2 I have the historical data for each month, column A holds the past, present and future months.

The old XML code looks up the date in cell B5, copies the values from several cells on sheet 1 (B:10, B:12, B:15, B:16, etc… no systematics here) then looks up the date from cell B5 on sheet1 in column A on sheet2 and pastes the values from the B-cells into the row on sheet 2 with the matching date: e.g. sheet1-B:10 goes to sheet2-column B, Sheet1-B12 goes to Sheet2-column C, and so forth. Hope this makes sense for you!

I would really appreciate if one of you has an idea for how I can solve this – I guess it should be rather basic... However I’ve read through the forum without much luck, as the entire VBA universe is very new to me.

BR and thanks!

 

[JLGWhiz]

The old XML code looks up the date in cell B5, copies the values from several cells on sheet 1 (B:10, B:12, B:15, B:16, etc… no systematics here)

Hi, McMuffin, Welcome to the Forum.

If you do not know what the criteria is for copying these cells, I am afraid you will not have much luck getting help with the code. VBA, like all programming languages, relies on logic, values, locations and events to accomplish successful execution of procedures. For someone to assist you, they would need to know what causes the certain cells to be copied (criteria), where that information is derived from (location, Column, Row, etc) and what determines where it gets pasted (logical match to existing data, or user designated, etc.). Does the user activate the procedure (macro) or is it event driven? In other words, why was the procedure developed to begin with and what does it really do. If you can provide that, I am sure somebody will try to help with a solution to your problem.

 

[JLGWhiz]

The old XML code looks up the date in cell B5, copies the values from several cells on sheet 1 (B:10, B:12, B:15, B:16, etc… no systematics here)

Duplicate post

 

[McMuffin]

Hi, McMuffin, Welcome to the Forum.

If you do not know what the criteria is for copying these cells, I am afraid you will not have much luck getting help with the code. VBA, like all programming languages, relies on logic, values, locations and events to accomplish successful execution of procedures. For someone to assist you, they would need to know what causes the certain cells to be copied (criteria), where that information is derived from (location, Column, Row, etc) and what determines where it gets pasted (logical match to existing data, or user designated, etc.). Does the user activate the procedure (macro) or is it event driven? In other words, why was the procedure developed to begin with and what does it really do. If you can provide that, I am sure somebody will try to help with a solution to your problem.

Hi JLGWhiz

thanks for the comments, I'll try to explain as good as I can and be a little more concrete:

There is a "macro button" in sheet1. When it is pressed the cells B:10, B:12, B:15, B:16, B:17, B18, B:32, B:33, B:40 is copied and pasted (the value, not the formula) into sheet2.
The cells from column B is pasted into a row on sheet2, which row is determined by cell B:5 on sheet1 (that is: the macro finds the date from B:5 in column A on sheet2, if this cell is A:9 the macro will paste the following values:

B:10 (sheet1) into B:9 (sheet2)
B:12 (sheet1) into C:9 (sheet2)
B:15 (sheet1) into D:9 (sheet2)
B:16 (sheet1) into E:9 (sheet2)
B:17 (sheet1) into F:9 (sheet2)
B:18 (sheet1) into G:9 (sheet2)
B:32 (sheet1) into H:9 (sheet2)
B:33 (sheet1) into I:9 (sheet2)
B:40 (sheet1) into J:9 (sheet2)

I hope this made sence for you! Otherwise let me know...

(it wold be so nice if this site had an option for uploading excel files and let other users view them - much easier than all of this explaning...)

 

[JLGWhiz]

See if this will work for you. Copy the procedure to your standard code module1. Access the code module by pressing Alt + F11. You can the insert a button or use the old button by reassigning it to this procedure. See Excel Help for step by step instructions on that. Also be sure the sheet names are correct for your files.

Sub bCol()
Dim sh1 As Worksheet, sh2 As Worksheet, rng As Range, c As Range
Set sh1 = Sheets("Sheet1") 'Edit sheet name
Set sh2 = Sheets("Sheet2") 'Edit sheet name
With sh1
    Set rng = Union(.Range("B10"), .Range("B12"), .Range("B15:B18"), .Range("B32:B33"), .Range("B40"))
End With
rng.Copy
Set c = sh2.Range("A:A").Find(sh1.Range("B5").Value, LookIn:=xlValues)
    If Not c Is Nothing Then
        c.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Transpose:=True
    End If
End Sub

 

[McMuffin]

Works perfectly! Thank you so much

 

[McMuffin]

Hi JLGWhiz

I've modified your code to copy the range D16 to G16 on sheet1 and paste this into the row on sheet2 that holds the matching date in column A, but it is not working for me. Do you know why?

Sub bCol()
Dim sh1 As Worksheet, sh2 As Worksheet, rng As Range, c As Range
Set sh1 = Sheets("Sheet1") 'Edit sheet name
Set sh2 = Sheets("Sheet2") 'Edit sheet name
With sh1
Set rng = Union(.Range("D16"), .Range("E16"), .Range("F16"), .Range("G16"))
End With
rng.Copy
Set c = sh2.Range("A:A").Find(sh1.Range("D12").Value, LookIn:=xlValues)
If Not c Is Nothing Then
c.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Transpose:=False
End If
End Sub

 

[JLGWhiz]

Hi JLGWhiz

I've modified your code to copy the range D16 to G16 on sheet1 and paste this into the row on sheet2 that holds the matching date in column A, but it is not working for me. Do you know why?

Sub bCol()
Dim sh1 As Worksheet, sh2 As Worksheet, rng As Range, c As Range
Set sh1 = Sheets("Sheet1") 'Edit sheet name
Set sh2 = Sheets("Sheet2") 'Edit sheet name
With sh1
Set rng = Union(.Range("D16"), .Range("E16"), .Range("F16"), .Range("G16"))
End With
rng.Copy
Set c = sh2.Range("A:A").Find(sh1.Range("D12").Value, LookIn:=xlValues)
If Not c Is Nothing Then
c.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Transpose:=False
End If
End Sub

Two different animals. Condition one had non-contiguous cells in a column (vertical) which required the Union function to join them as a contiguous range and then transpose the range to a row (horizontal). Condition two has a contiguous range in a row (horizontal) which will be copied to a row (horizontal) and neither needs the Union function nor the Transpose operation.

P.S. It is not a very good technical description of the problem to say, "The code didn't work". It is more helpful in performing analysis of the problem if the error message received is spelled out, or a description of what the result was versus what was expected is spelled out.

If there is a date value in "D12" of sheet 1 then this should work. The previous condition had the criteria range as B5.

Sub bCol2()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("Sheet1") 'Edit sheet name
Set sh2 = Sheets("Sheet2") 'Edit sheet name
sh1.Range("D16:E16").Copy
Set c = sh2.Range("A:A").Find(sh1.Range("D12").Value, LookIn:=xlValues)
    If Not c Is Nothing Then
        c.Offset(0, 1).PasteSpecial Paste:=xlPasteValues
    End If
End Sub

 

[McMuffin]

Thank you so much - Didn't know the Union function! Seems to work now