I have a complicated set of requirements and am trying to develop an appropriate piece of Excel VBA code. I am a beginner so any advice on the issue would be much appreciated.
CONTEXT: "Company A" sheet lists the Department, the pay scale and the job title (columns A-C). "Company B" sheet lists all the above for their jobs but also, in columns D and E, specifies whether each job is a match for a Company A job (column D) and if so, for which one (column E), or whether there is no match, and then column E cell is empty.
AIM: I want to match jobs in Company B to those in Company A and copy/paste those matched Company B jobs over to "Company A" sheet as links in columns D-F but there is a PROBLEM!
PROBLEM: In each company there are multiple jobs with the same Department-Pay Grade-Job title combo and those numbers do not match across the companies, e.g. there are 4 jobs in company A but only 3 matched ones in Company B. I would want my code to know to copy the company B jobs only three times and leave the fourth row empty (to visually indicate the three matching jobs and the fourth one with no match). To achieve that, I have the number of jobs on the same pay grade in each department in each company and want to use those numbers as counters for my Do While loop.
The code I wrote:
Private Sub CommandButton6_Click()
Dim counter As Integer
Dim myNum As Integer
a = Worksheets("COMPANYB").Range("A" & Rows.Count).End(xlUp).Row
Worksheets("JobNumbers").Range("D:D").Value = counter
Worksheets("JobNumbers").Range("C:C").Value = myNum
Do While counter < myNum
For i = 2 To a
If Worksheets("COMPANYB").Range("D" & i).Value = "Match" And _
Worksheets("COMAPNYB").Range("E" & i).Value = Worksheets("COMPANYA").Range("C" & i).Value Then
Worksheets("COMPANYB").Range("A" & i & ":C" & i).Copy
Worksheets("COMPANYA").Activate
b = Worksheets("COMPANYA").Range("D" & Rows.Count).End(xlUp).Offset(0, 3).Row
Worksheets("COMPANYA").Range("D" & b + 1).Select
ActiveSheet.Paste Link:=True
End If
Next i
counter = counter + 1
myNum = myNum - 1
Loop
End Sub
...but instead of working, it just puts all 0 in my "JobNumbers" sheet in columns from where I want it to take my counter and myNum to set the test for the Do While loop. As you can see I didn't even get to the 'leave the rows blank when copying' thing I want to do because I'm super stuck with this. I'm also starting to wonder whether I'm not expecting too much from VBA...
I do recognise this is a lot of text and I do apologise - I'd appreciate any feedback/help, thanks!
The = is an assignment operator. The reason it writes "0" into every cell in the column is because Worksheets("JobNumbers").Range("D:D").Value refers to every cell in the entire column D. Then you assigned = your variable counter to all of those cells by writing Worksheets("JobNumbers").Range("D:D").Value = counter
Above that, you defined counter as an Integer, but did not assign = a value to it, so it started empty and doesn't change. And empty integer assigned to a cell's value will leave that cell at 0.