PDA

View Full Version : [SOLVED] Beginner help on creating excel visual tool through VBA



thk12205
10-06-2017, 06:42 PM
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
10-07-2017, 04:28 AM
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
10-07-2017, 01:37 PM
Thank you Mdmackillop. :content: This is so streamlined and effective. I tested it out and believe it is the answer I've been searching for!

mdmackillop
10-07-2017, 01:48 PM
Happy to help. Which option suited?

thk12205
10-07-2017, 02:56 PM
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.