PDA

View Full Version : VBA to search in sheet1, match data in sheet2, return search results in sheet1



jake1124
01-24-2017, 04:29 PM
Hey guys!

I'm a beginner with basic java coding and a huge passion to learn. I have been practicing and learning as I go but I can't get this macro to work... if it's even possible. I'm sure there's a much easier way to do it so I'm looking for help :)

Macro Intention - Match the 2 criteria from sheet 1, from the 2 criteria on sheet 2 then return matching/not matching if the third criteria is true
In sheet 2 find the Row on Column A that contains "Code". Take note of that Row


In sheet 1 look at Column C - Visit Name THEN in sheet 2 Look at Row(Code), Column X(Visit Names). Find matching visit name. Mark down that column
In sheet 2 look at Row(Code), Column + 1 - Procedures. Mark down that range
In sheet 1 look at Column D - Procedure Name THEN in sheet 2 Look at Range(Procedures). Row + 1 until we find partially matching procedure name (as it wont be exact). Mark down that range
In sheet 1 look at Column E - Cost THEN in sheet 2 find the corresponding Row for correct procedure and column for correct visit. If they match then go to sheet 1: Range "I2" = "Matching" if exact, "Not matching" if not
Repeat for each line




Refer to the images to reference an example of what sheet1 and sheet 2 look like


Sub SearchingForValue()

Dim i As Integer
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Sheet1")
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("Sheet2")

Dim sRow As Integer, sCol As Integer
Dim sRng As Range

Set sRng = Range("C2")
sRow = sRng.Row
sCol = sRng.Column

Dim fRow As Integer, fCol As Integer
Dim fRng As Range

Set fRng = Range("A1")
fRow = fRng.Row
fCol = fRng.Column

Dim nRow As Integer, nCol As Integer
Dim nRng As Range



Dim jRow As Integer, jCol As Integer
Dim jRng As Range



Dim tRow As Integer, tCol As Integer
Dim tRng As Range



Dim qRow As Integer, qCol As Integer
Dim qRng As Range

Set qRng = Range("I2")
qRow = qRng.Row
qCol = qRng.Column

'
Do
'
If ws2.Cells(fRow, fCol).Text = "Code" Then
Set tRng = ws2.Cells(fRow, fCol)
tRow = tRng.Row
tCol = tRng.Column
Else
fRow = fRow + 1
End If

If ws1.Cells(sRow, sCol) = ws2.Cells(fRow, fCol) Then
Set nRng = ws2.Cells(fRow, fCol)
nRow = nRng.Row
nCol = nRng.Column
Else
fCol = fCol + 1
End If

If ws1.Cells(sRow, sCol + 1).Find(what:=Text,LookAt:=xlPart) = ws2.Cells(tRow, tCol + 1).Find(what:=Text,LookAt:=xlPart) Then
Set jRng = ws2.Cells(tRow, tCol + 1)
jRow = jRng.Row
jCol = jRng.Column
Else
tRow = tRow + 1
End If

If ws1.Cells(sRow, sCol + 2) = ws2.Cells(jRow, nCol) Then
ws1.Cells(qRow, qCol).Activate
ActiveCell.Text = "Matching"
Else
ws1.Cells(qRow, qCol).Activate
ActiveCell.Text = "Not Matching"
End If

If ws1.Cells(sRow + 1, sCol) <> ws1.Cells(sRow, sCol) Then
sRow = sRow + 3
qRow = qRow + 3
Else
sRow = sRow + 1
qRow = qRow + 1
End If
'
Loop While Not ws1.Cells(sRow, sCol).Text = ""
'

End Sub


18125
18126

SamT
01-24-2017, 05:53 PM
I assume that you are confusing "Column" and "Cell" in your description. I will further assume that unless you specifically name a column, that you mean "Cell."

In your description you have named all the columns "Another Column" That is not sufficient to decipher your request.

In your code, you have named all the variables with the same cryptic names, varying only in the randomly chosen first letter. That is not sufficient to decipher your request. See better examples below.

Is the data structure on sheet 2 invariant?


Dim CodeCol As Range
Dim ProcedureCol As Range
Dim PhysicalRow as Long

Set CodeCol = ws2.Rows(3).Find("Code").EntireColumn
Set ProcedureCol = ws2.Rows(3).Find("Procedure").EntireColumn

PhysicalRow = ProcedureCol.Find("Physical").Row 'ProcedureCol already refers only to ws2

jake1124
01-24-2017, 06:32 PM
I assume that you are confusing "Column" and "Cell" in your description. I will further assume that unless you specifically name a column, that you mean "Cell."

In your description you have named all the columns "Another Column" That is not sufficient to decipher your request.

In your code, you have named all the variables with the same cryptic names, varying only in the randomly chosen first letter. That is not sufficient to decipher your request. See better examples below.

Is the data structure on sheet 2 invariant?


Dim CodeCol As Range
Dim ProcedureCol As Range
Dim PhysicalRow as Long

Set CodeCol = ws2.Rows(3).Find("Code").EntireColumn
Set ProcedureCol = ws2.Rows(3).Find("Procedure").EntireColumn

PhysicalRow = ProcedureCol.Find("Physical").Row 'ProcedureCol already refers only to ws2


Hey Sam, to clarify, I edited the Macro Intention to hopefully be a little more clear. The format on sheet 1 will always remain the same. Sheet 2 will always be different, a part from the location of Procedures and Visit Names, after finding "Code"

SamT
01-24-2017, 09:45 PM
So Visit columns in Sheet2 are Costs. Now it makes sense

What, if any, is the maximum number of visits?

Are the Visit Names really "Visit1," Visit2,". . . "Visitn?"

I would expect them to be dates, but it doesn't matter except to make coding easier. As long as they are identical on both sheets.

The two Procedure fields with "blood" in them cannot be easily matched in code. Is there a complete comparison/match/cross reference for the two Procedure lists?

Depending on the length of the cross reference, it will be best to use either, a worksheet table, or a Select Case, or a Collection, Dictionary, or Array. Or even a combination of them.

After that, the coding will use pretty common constructs.

After thinking about it, IMO, build a Collection where the Index is the Sheet1 Procedure Names and the Items are Discovered Row numbers of Sheet2.

jake1124
01-25-2017, 06:29 AM
The number of different visits will vary. The most unique visit names I've seen was around 30.

The visit names are always different, but will match exactly across sheet 1 and sheet 2. They are labeled C1D1, C2D2, W2D5, SV, etc.. However, I'm only interested in seeing that the cost matches from sheet 1 Visit-Procedure to the matching visit-procedure on sheet 2. So I remove duplicates on sheet 1 so that there will always be a unique procedure per visit name for the macro to check.

There is a separate list which classifies the exact text that blood/lab(sheet1) corresponds to on sheet2. With that being said, and your advice, I would need to build the collection to match the procedure-index from sheet1 to the corresponding procedure on sheet 2 for every procedure?
Now out of curiosity, if per-say Blood/Lab (sheet 1) is the sum from the 2 costs of procedure Blood and procedure Lab on sheet 2, is this possible to classify in the collection as well?

Edit: Can you recommend any links to similar samples/templates of a collection code for me to refer to while I learn and trial/error?

SamT
01-25-2017, 10:44 AM
The visit names are always different, but will match exactly across sheet 1 and sheet 2. So you can use an array for the sheet2 column numbers.


There is a separate list which classifies the exact text that blood/lab(sheet1) corresponds to on sheet2.
Excellent.

Here is a VBAX Member's site: VBA for smarties (http://www.snb-vba.eu/index_en.html): Scroll down the "Contents" to DICTIONARY, ARRAY, COLLECTION, ARRAYLIST

Some Variables I would use


VisitsColumnOffset As Long ' to hold the Column Number of the column just before the first "Visit" column on sheet2
VisitsColumns As Variant 'Add the VisitsColumnOffset to the appropriate index to find the column number
ProceduresRowOffset as Long 'Same as the preceding: for Procedure's Rows
XRef As Variant 'A Range type Array to hold the Cross reference list. Used to fill the ProcedureRows Collection
ProcedureRows As Collection


It is my preference to use long explicit variable names. YMMV, for example, you might use VcolOff and ProwOff or even,(Shudder,) X and Y

I would declare all of those, excwept XRef, as Module level Variables.

I would use a Main VBA Procedure, a Sub or Function, to call a separate Sub, "InitVars," that would load the values into those Module level Variables, then would use those variable to do the actual work on Sheet1.

InitVars would set the values of the first three Variables above, since those are simple Finds, then it would call InitProcedureRows.

Crudely and incompletly

Dim Temp As Range
Set Temp = Sheet2.Find("Procedure")
ProceduresRowOffset = Temp.Column - 1
VisitsColumnOffset = Temp.End(xlToRight).Column - 1
VisitsColumns = Range(Temp.End(xlToRight), Temp.End(xlToRight).End(xlToRight)).Value


InitProcedureRows has the rather complex task of loading that collection. It holds the variable XRef, and starts by setting it to the cross reference table/range. as per VBA For Smarties.

Then it can fill the Rows Collection

Crudely and incompletly

For i = 1 to UBound(XRef)
Rw = Sheet2.ProceduresColumn.Find(XRef(i, 2).Row
ProcedureRows.Add Xref(i, 1), Rw
Next i


A Function to set the Col Variable in the Main Sub. Incomplete

Private Function GetCol(Visit as String) As Long
For i = 1 to Ubound VisitsColumns
If VisitsColumns(i) = Visit then
GetCol = i + VisitsColumnOffset
Exit Function
Next i
End Function

As you go down the Cells in Sheet1 "Visits," If the cells is empty, set Col to zero, and if Col = 0 and Cell is not empty, Col = GetCol(Cell).

Getting the Row number is

Rw = ProcedureRows(Cell.Offset(, 1)) + ProceduresRowOffset

getting the cost is

Cost = Sheet1.Cells(Rw,Col)

SamT
01-25-2017, 10:50 AM
I hope that helps.

jake1124
01-26-2017, 09:57 AM
Hey Sam,

Thanks again for all the help! After thinking about this for awhile yesterday, I figured out a way to make this a million times easier by reformatting sheet 1 (with a very simple macro) so the initial macro can completely skip the step of searching for the procedure and rows column.

Sheet 2 Format Macro - Puts The Visit Columns in Row 1, Puts the Procedure Rows in Column A
Sheet 2 TrimAll Macro - Removes spaces after every string in Column A, sheet 2, to ensure the replace macro works properly
Sheet 2 ReplaceProcedureNames Macro - Finds the exact wording for procedures in Column A, sheet 2 and replaces it what the text sheet 1 will use. -However there is one issue I am facing that keeps giving me a mismatch error on only one procedure. Every other procedure replaces the cell's value to the proposed text. (refer to the first "chemistry" replace line).


Sub ReplaceProcedureNames()


With Worksheets("Sheet2").Columns("A")
.Replace "Informed consent", "Informed consent", xlWhole
.Replace "Inclusion/Exclusion", "Informed consent", xlWhole
.Replace "Demographics", "Informed consent", xlWhole

.Replace "Complete Physical Exam", "Physical Exam", xlWhole
.Replace "Limited Physical Exam", "Physical Exam", xlWhole

.Replace "Medical History including Disease History", "Medical History", xlWhole

.Replace "12 Lead ECG", "ECG", xlWhole
.Replace "Triplicate 12 Lead ECG", "ECG", xlWhole

.Replace "Performance Status (ECOG)", "ECOG", xlWhole

.Replace "Vital Signs", "Vital Signs", xlWhole

.Replace "Serum pregnancy test", "Serum Pregnancy", xlWhole

.Replace "STAT Laboratory Fees", "BLOOD DRAW/LAB HANDLING", xlWhole
.Replace "Venipuncture ", "BLOOD DRAW/LAB HANDLING", xlWhole
.Replace "RECIST, Tumor Measurements", "BLOOD DRAW/LAB HANDLING", xlWhole
.Replace "Serum or Plasma for Pharmacokinetics-ABBV-085, Total mAB, MMAE", "BLOOD DRAW/LAB HANDLING", xlWhole
.Replace "Serum or Plasma for Anti-drug Antibody", "BLOOD DRAW/LAB HANDLING", xlWhole
.Replace "Serum for soluble LRRC15", "BLOOD DRAW/LAB HANDLING", xlWhole
.Replace "Plasma for pharmacodynamic and response markers", "BLOOD DRAW/LAB HANDLING", xlWhole
.Replace "Plasma for circulating free DNA", "BLOOD DRAW/LAB HANDLING", xlWhole

'.Replace "Chemistry: Blood Urea Nitrogen (BUN), Creatinine, Total Bilirubin, Albumin, Aspartate amino transferase (AST), Alanine amino transferase (ALT), Alkaline Phosphatase, Sodium, Potassium, Calcium, Total Protein, Sodium bicarbonate, Glucose, Chloride, Bicarbonate/CO2", "Chemistry", x1Whole
.Replace "Chemistry: Direct Bilirubin", "Chemistry", xlWhole
.Replace "Chemistry: Phosphorus", "Chemistry", xlWhole
.Replace "Chemistry: Uric acid", "Chemistry", xlWhole

End With
End Sub


Now after those macros using some of the ideas you proposed above, this is where I am stuck.


Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Sheet1")
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("Sheet2")


Dim ISV As Range 'Invoice Starting Visit (Sheet1)
Dim ISP As Range 'Invoice Starting Procedure (Sheet1)
Dim IC As Range 'Invoice Cost (Sheet1)

Set ISV = ws1.Range("C2")
Set ISP = ws1.Range("D2")
Set IC = ws1.Range("E2")

'Finding Range of Invoice Visit and Procedure on Sheet2
Dim TempVisit As Range
Set TempVisit = ws2.Rows(1).Find(ISV.Value)

Dim TempProcedure As Range
Set TempProcedure = ws2.Columns("A").Find(ISP.Value)

Dim DupeProcedure As Range

'Temp storing duplicate procedurs names row
Dim i As Integer

For i = 1 To UBound(DupeProcedure)
If ws2.Find(TempProcedure.Value) = Only one cell Then
Else

How can I make this match the correct visit from sheet 1 to the correct visit to sheet 2. Then search the procedure (for that visit) on sheet 1, then look at sheet 2 and see if there are multiple rows that matches the procedure. IF there is, then to temporarily record those rows in order to move on to the final request of the code. Then to refer back to sheet 1 find the cost for the visit-procedure then to sheet 2 and match the cost for the same visit-procedure (AND sum them if there are duplicated procedures), return "Matching" or "Not Matching" on sheet 1 column I next to the row that we looked at. Then to repeat for each row?

SamT
01-26-2017, 11:10 AM
Sheet 2 TrimAll Macro - Removes spaces after every string in Column A, sheet 2, to ensure the replace macro works properly??? That's not reflected in the replace macro


'.Replace "Chemistry: Blood Urea Nitrogen (BUN), Creatinine, Total Bilirubin, Albumin, Aspartate amino transferase (AST), Alanine amino transferase (ALT), Alkaline Phosphatase, Sodium, Potassium, Calcium, Total Protein, Sodium bicarbonate, Glucose, Chloride, Bicarbonate/CO2", "Chemistry", x1Whole

Try breaking it down

.Replace "Chemistry: Blood Urea Nitrogen,", ""
.Replace "Creatinine, Total Bilirubin, Albumin,", ""
.Replace " Aspartate amino transferase (AST),", ""
.Replace " Alanine amino transferase (ALT),", ""
.Replace " Alkaline Phosphatase, Sodium, Potassium, Calcium,", ""
.Replace "Total Protein, Sodium bicarbonate, Glucose, Chloride, Bicarbonate/CO2,", ""
.Replace "(BUN),", "Chemistry"
At the least, you will isolate the problem


Now after those macros using some of the ideas you proposed above, this is where I am stuck.
And, spend some more time at the VBA For Smarties Site looking at Arrays.

jake1124
01-26-2017, 06:36 PM
??? That's not reflected in the replace macro

It is now :P




Try breaking it down

.Replace "Chemistry: Blood Urea Nitrogen,", ""
.Replace "Creatinine, Total Bilirubin, Albumin,", ""
.Replace " Aspartate amino transferase (AST),", ""
.Replace " Alanine amino transferase (ALT),", ""
.Replace " Alkaline Phosphatase, Sodium, Potassium, Calcium,", ""
.Replace "Total Protein, Sodium bicarbonate, Glucose, Chloride, Bicarbonate/CO2,", ""
.Replace "(BUN),", "Chemistry"

This didn't work either for some reason. Is there a possibility that for some reason that entire line is formatted differently?



And, spend some more time at the VBA For Smarties Site looking at Arrays.

I've managed to get the Visits on sheet 2 in one array, as well as procedures on sheet 2 in a different array. I tested an "IF AND" statement that managed to pull the all the matching values and return a cost in a cell back on Sheet1. The rest should be straight forward as it will be just tweaking and hopefully adding a couple other workarounds. Until then, I hope you don't mind me popping back in the thread and stealing your knowledge when I get stuck.

SamT
01-26-2017, 08:48 PM
This didn't work either for some reason. Is there a possibility that for some reason that entire line is formatted differently?What about the spaces?

No change at all?

Brute Force, Treat that Procedure separately with

Dim Found as Range
Do
set Found = Worksheets("Sheet2").Columns("A").Find("BUN")
If Not Found Is Nothing then Found = "Chemistry"
Loop While Not Found is Nothing

jake1124
01-27-2017, 10:16 AM
What about the spaces?

No change at all?

Brute Force, Treat that Procedure separately with

Dim Found as Range
Do
set Found = Worksheets("Sheet2").Columns("A").Find("BUN")
If Not Found Is Nothing then Found = "Chemistry"
Loop While Not Found is Nothing

The Brute Force method worked! Thank you.

Do you mind reviewing this and tell me what you think thus far? I'm stuck on how to add the values of the duplicate procedures from Sheet 2. However the logic behind the rest of how the code should operate makes sense to me.


Sub Test1()



Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Sheet1")
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("Sheet2")


Dim ISV As Range 'Invoice Starting Visit (Sheet1)
Dim ISP As Range 'Invoice Starting Procedure (Sheet1)
Dim IC As Range 'Invoice Cost (Sheet1)
Dim BC As Range 'Budget Cost (From sheet2)

Set ISV = ws1.Range("C2")
Set ISP = ws1.Range("D2")
Set IC = ws1.Range("E2")
Set BC = ws1.Range("I2")

Dim n As Integer 'This will be associated with ISV to loop to next row
Dim p As Integer 'This will be associated with ISP to loop to next row
Dim l As Integer 'This will be associated with IC to loop to next row
Dim c As Integer 'This will be associated with BC to loop to next row

n = ISV.Row
p = ISP.Row
l = IC.Row
c = BC.Row

'Finding Range of Invoice Visit and Procedure on Sheet2
'Dim TempVisit As Range
'Set TempVisit = ws2.Rows(1).Find(ISV.Value)

'Dim TempProcedure As Range
'Set TempProcedure = ws2.Columns("A").Find(ISP.Value)

'Dim DupeProcedure As Range
'Temp storing duplicate procedurs names row

Dim PArr(1 To 100) As Range 'Sheet2 Procedures Range Array
Dim i As Integer
Dim PStr() As String 'Sheet2 Procedure String Array
Dim DPstr() As String 'Sheet2 Procedure Dupe Array
Dim t As Integer 'Integer to count the loop of totals of the dupe visits
Dim counter As Integer 'Integer to store the sum of duped totals


With ws2
For i = 1 To UBound(PArr)
Set PStr(i) = ws2.Cells(i, 1)
If PStr(i) = ws2.Cells(i + 1, 1) Then
Set PStr(i) = 0
Set DPstr(i) = ws2.Cells(i, 1)
End If
Next i
End With

Dim VArr(1 To 100) As Range 'Sheet2 Visits Array
Dim j As Integer
Dim VStr() As String

With ws2
For j = 1 To UBound(VArr)
Set VStr(j) = ws2.Cells(1, j)
Next j
End With

With ws2
For b = 1 To 50
If p.Value = DPstr.Find(p.Value) And n.Value = VStr.Find(n.Value) Then
Call DupeCounterFunction
p = p + 1
n = n + 1
l = l + 1
c = c + 1
ElseIF
p.Value = PStr.Find(p.Value) And n.Value = Vstr.Find(n.Value) Then
If l.Value = ws2.Cells(PStr(p.Value).Address.Row, VStr(n.Value).Address.Column) Then
c.Value = "Matching"
p = p + 1
n = n + 1
l = l + 1
c = c + 1
Else
c.Value = "Not Matching"
p = p + 1
n = n + 1
l = l + 1
c = c + 1
End If
Next b
Loop While Not p+1.Text And p+2.Text And p+3.Text <> ""





'DupeCounterFunction: If there is a duplicate procedure then Add the Row and Column value from sheet 2. Then look to see if DPstr next still equals p.value. Add to counter if true. etc. Then see if ws1.l.Value matches. If true then c.Value = "Matching" if False then c.Value = "Not Matching". next b
'For t = 1 To 10
' counter = ws2.Cells(DPstr(p.Value).Address.Row, VStr(n.Value).Address.Column)
' FindNext (DPstr(p.Value)) <> p.Value
'Afterwards: If l.value = counter Then
'c.Value = "Matching"
'Else
'c.Value = "Not Matching"
'End Function

'''SINGLE MATCH - n.Value = VArr(.Find(n.Value)).Value And p.Value = PArr(.Find(p.Value)).Value Then
''' c.Value = Cells(VArr(.Find(n.Value)).Address.Row, PArr(.Find(p.Value)).Address.Column)


'If ISV.Value = VArr(.Find(ISV.Value)).Value And ISP.Value = PArr(.Find(ISP.Value)).Value Then
' Set VArr


'''''' THIS LOOP WORKS '''';
'If ISV.Value = VArr(13).Value And ISP.Value = PArr(5).Value Then
' BC.Activate
' ActiveCell.Value = 100 'ws2.Cells(i, j).Value
' Else
' BC.Activate
' ActiveCell.Value = PArr(5).Value
'End If

End Sub

SamT
01-27-2017, 12:27 PM
Dim BC As Range 'Budget Cost (From sheet2)
Set BC = ws1.Range("I2")
???

Make it a habit to declare Row, Column, and now, Sheet counters as Long
Dim n As Integer 'This will be associated with ISV to loop to next row
Dim p As Long 'Integer 'This will be associated with ISP to loop to next row
Dim l As Integer 'This will be associated with IC to loop to next row
Dim c As Integer 'This will be associated with BC to loop to next row


PArr is null ATT and it's UBound is declared as 99, (100 counting from 0,) and its LBound is 0.

With ws2
For i = 1 To UBound(PArr)
When looping thru an array, it is best to use From: LBound To: UBound.

SamT
01-27-2017, 12:27 PM
GIGO Too many open Bracketing structures
For b = 1 To 50
If p.Value = DPstr.Find(p.Value) And n.Value = VStr.Find(n.Value) Then
Call DupeCounterFunction
p = p + 1
n = n + 1
l = l + 1
c = c + 1
ElseIF
p.Value = PStr.Find(p.Value) And n.Value = Vstr.Find(n.Value) Then
If l.Value = ws2.Cells(PStr(p.Value).Address.Row, VStr(n.Value).Address.Column) Then
c.Value = "Matching"
p = p + 1
n = n + 1
l = l + 1
c = c + 1
Else
c.Value = "Not Matching"
p = p + 1
n = n + 1
l = l + 1
c = c + 1
End If
Next b
Loop While Not p+1.Text And p+2.Text And p+3.Text <> ""


Use of this VBA Code Indenter - Add-ins for Microsoft Excel (https://www.add-ins.com/macro-products-for-Microsoft-Excel/how-to-indent-vba-code/how-to-indent-vba-code.htm) can make these open Containers easier to spot since the indenting will be messed up

SamT
01-27-2017, 12:56 PM
Before we go any farther...

In the VBA Editor (VBIDE), Tools Menu >> Options >> Editor Tab, check all Optionbuttons in the Code Settings Frame.

On the General Tab, Check
Notify Before StateLoss
Break on All Errors
Compile On Demand
Background Compile
and Show ToolTips

When developing a long procedure like that, work and perfect one section at a a time. Be sure to use the Debug menu >> Compile whenever you have any small bit of code written. For example, After declaring any and all variables you think you might use.

After writing any loop structure
After writing any If... then structure
After writing any With structure.
After writing any Function (and see below)

The structures do not have to contain all the code before compiling.Ex, Compile at this point

For i = LBound(arVar) to UBound(arVar)
Next i
Compile again here

For i = LBound(arVar) to UBound(arVar)
If X = Y Then
Else
End If
Next i
And here

For i = LBound(arVar) to UBound(arVar)
If X = Y Then
arrVar(i) = X
Else
End If
Next i

The more you code, the less you will need to compile because you will get better at coding and better at eyeballing mistakes.

My most often written sub is

Sub t()
Dim x
X = (some line of code I am trying to fix)
End subThese are called "Stubs" and are also the only way to test Functions before all the code is completed.


Sub t()
Dim x
X = myFunc(42)
End sub

Function MyFunc(Y as Long) as Double
MyFunc = Y / 7
End Function

I also use the X-Variable in regular code for troubleshooting. Using a bit of your code above for an example

With ws2
Dim X
For i = 1 To UBound(PArr)

X = ws2.Cells(i, 1)
PStr(i) = ws2.Cells(i, 1)
X = PStr(i)

X = ws2.Cells(i + 1, 1)
If PStr(i) = ws2.Cells(i + 1, 1) Then
PStr(i) = 0
X = PStr(i)

DPstr(i) = ws2.Cells(i, 1)
X = DPstr(i)
End If
Next i
End With
Then, as I use F8 to step thru the code, I keep the mouse hovering over one of the X's to see it's value.