Macro Help

[alozano]

Hello All,

I've done basic VBA coding in college but we didn't really focus on VBA excel. I'm trying to create an automatic fill up macro based on data.
So my starting point, I'm already stuck!

The plan is based on the "X" category, the value of Y and Z will pop up on a daily basis on another worksheet..
I'm trying to do something simple for now but can't seem to grasp it..

Private Sub CommandButton21_Click()
Dim x As String
Dim y As String
Dim z As String
x = Range("C:C").Value
y = Range("D:D").Value
z = Range("A:A").Value
If x = "Office" Then y = z

End Sub

So the main code i'm stuck at is , if the X range is saying "Office" on the Y column, it should populate the Z value?

Thanks in advance !

[Kenneth Hobs]

Welcome to the forum!

Iterating 3 million cells might not be the most efficient. Most deal with data much smaller. I can show you how to code it but do you really need it? A simple formula would accomplish the same goal.
e.g. in D2 and then fill down: =If(C2="Office",A2,"")

[alozano]

Welcome to the forum!

Iterating 3 million cells might not be the most efficient. Most deal with data much smaller. I can show you how to code it but do you really need it? A simple formula would accomplish the same goal.
e.g. in D2 and then fill down: =If(C2="Office",A2,"")
Thanks for the welcome! Hoping to learn a lot in this forum

Agreed - It was just a start sample. And yes a simple formula would work but the whole goal is to have it a daily kind of macro. So lets say for Day 1, A msg box will ask me which data table I should select. From there it'll populate to all different worksheet based on the Input of "Office" "Chair" "table" to different worksheets of "office" , "chair", and "table" work sheets.. I knew how to do this kind of coding in visio but not in excel :(

Does that make sense or am I just babbling? hehe

[Kenneth Hobs]

There are two ways that one might do it via VBA. (1) Place the values into column D cells or (2) place the formula that I just showed you. In both approaches, rather that doing the whole columns, I would just count the range in column A from A2 to the last cell in column A with a value. From there, we can then do (1) or (2).

[Kenneth Hobs]

Case 2:

Private Sub CommandButton21_Click()
Dim r As Range, c As Range
Set r = Range("A2", Range("A" & Rows.Count).End(xlUp))

For Each c In r
With c
If .Offset(, 2).Value2 = "Office" Then
.Offset(, 3).Value = .Value
Else
.Offset(, 3).Value = ""
End If
End With
Next c
End Sub