Beginner help on creating excel visual tool through VBA

[thk12205]

Version: Excel 2010 Professional Plus


Hi, beginner VBA user here.

I received a unique request, which was to match abstract "z" values attached to an x-y axis under an opaque isometric CAD visual. (in theory A,B,C would be 1,2,3 on x-axis, and 1,2,3 would be 1,2,3 on y-axis)

The x,y,z output is just three arrays outputted by the CAD program into excel, and the 3 values on each row make a coordinate and value (the first column x-value, the second column y-value, the third column z-value)

So the question is: Is there a simple method using VBA code to read the value from the x array and y array, and place the corresponding z array value (which is on the same row as the x and y array values) into its corresponding cell in another sheet?

So far I've been using the record macro tool, but for this case, I would greatly appreciate if someone could share with me a better/faster/cleaner method using VBA.

[mdmackillop]

Here are 2 options
Option 1 will use the cell reference for the position i.e 3,5,50 will put 50 into cell C5
Option 2 will create an origin (Black cell) in column 1 and offset from that to show relative locations. i.e. 3,5,10 would be 2 cells above 3,3,10.
I've added some colour (non-essential) for reference

Sub Test()
    Dim r As Range
    Dim ws As Worksheet
    Dim col As Long
  
    Set ws = Sheets(1)
    
    col = 2 'Set ColorIndex
   
    Set r = Range(ws.Cells(2, 1), ws.Cells(Rows.Count, 1).End(xlUp))
    
    Sheets(2).UsedRange.Clear
    For Each cel In r
        Sheets(2).Cells(cel.Offset(, 1), cel) = cel.Offset(, 2)


        'add colour
        col = col + 1
        cel.Interior.ColorIndex = col
        Sheets(2).Cells(cel.Offset(, 1), cel).Interior.ColorIndex = col
    Next
End Sub


Sub Test2()
    Dim r As Range
    Dim ws As Worksheet
    Dim Origin As Long
    Dim col As Long
    
    col = 2 'Set ColorIndex
    
    Set ws = Sheets(1)
    
    Origin = Application.Max(ws.Columns(2)) + 1
    
    Sheets(3).UsedRange.Clear
    Sheets(3).Cells(Origin, 1).Interior.ColorIndex = 1
    Set r = Range(ws.Cells(2, 1), ws.Cells(Rows.Count, 1).End(xlUp))


    For Each cel In r
        Sheets(3).Cells(Origin, 1).Offset(-cel.Offset(, 1), cel) = cel.Offset(, 2)

        'add colour
        col = col + 1
        cel.Interior.ColorIndex = col
        Sheets(3).Cells(Origin, 1).Offset(-cel.Offset(, 1), cel).Interior.ColorIndex = col
    Next
    
End Sub

[thk12205]

Thank you Mdmackillop. This is so streamlined and effective. I tested it out and believe it is the answer I've been searching for!

[mdmackillop]

Happy to help. Which option suited?

[thk12205]

Option 2 with a setup origin point suited the most. Thank you for catching it preemptively. It was a very useful iteration of option 1 that I forgot to specify.