Code Interpretation

[MrRJ]

Hello,
Can someone please help me interpret this code for me. What does each
section do and what is the function? The person who created this is no
longer here and I would like to understand how it works and possible make
adjustments if needed.

Thank you.

Sub CreateCSVFile()
Dim objWBPrice As Workbook
Dim objWBExport As Workbook
Dim objWSPrice As Worksheet
Dim objWSExport As Worksheet
Set objWBPrice = ActiveWorkbook
Set objWSPrice = objWBPrice.Worksheets("Price")
Set objWSExport = objWBPrice.Worksheets("CSV Export")
With objWSExport.Cells
.Clear
With .Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 11
.ColorIndex = xlAutomatic
End With
End With
Application.ScreenUpdating = False
j = 1
For i = 2 To objWSPrice.UsedRange.Rows.Count
If objWSPrice.Cells(i, 1).Value = "Y" Then
With objWSExport
.Cells(j, 1) = objWSPrice.Cells(i, 2).Value
.Cells(j, 2) = objWSPrice.Cells(i, 3).Value
.Cells(j, 3) = objWSPrice.Cells(i, 4).Value
.Cells(j, 4) = objWSPrice.Cells(i, 5).Value
.Cells(j, 5) = objWSPrice.Cells(i, 6).Value
.Cells(j, 6) = objWSPrice.Cells(i, 7).Value
.Cells(j, 7) = objWSPrice.Cells(i, 8).Value
.Cells(j, 8) = objWSPrice.Cells(i, 9).Value
.Cells(j, 9) = objWSPrice.Cells(i, 12).Value
.Cells(j, 10) = objWSPrice.Cells(i, 15).Value
.Cells(j, 11) = objWSPrice.Cells(i, 16).Value
.Cells(j, 12) = objWSPrice.Cells(i, 17).Value
.Cells(j, 13) = objWSPrice.Cells(i, 18).Value
.Cells(j, 14) = objWSPrice.Cells(i, 19).Value
.Cells(j, 15) = objWSPrice.Cells(i, 20).Value
.Cells(j, 16) = objWSPrice.Cells(i, 21).Value
.Cells(j, 17) = objWSPrice.Cells(i, 22).Value
.Cells(j, 18) = objWSPrice.Cells(i, 23).Value
.Cells(j, 19) = objWSPrice.Cells(i, 24).Value
.Cells(j, 20) = objWSPrice.Cells(i, 25).Value
.Cells(j, 21) = objWSPrice.Cells(i, 26).Value
.Cells(j, 22) = objWSPrice.Cells(i, 27).Value
.Cells(j, 23) = objWSPrice.Cells(i, 28).Value
.Cells(j, 24) = objWSPrice.Cells(i, 29).Value
.Cells(j, 25) = objWSPrice.Cells(i, 30).Value
.Cells(j, 26) = objWSPrice.Cells(i, 31).Value
.Cells(j, 27) = objWSPrice.Cells(i, 32).Value
.Cells(j, 28) = objWSPrice.Cells(i, 33).Value
.Cells(j, 29) = objWSPrice.Cells(i, 34).Value
.Cells(j, 30) = objWSPrice.Cells(i, 35).Value
.Cells(j, 31) = objWSPrice.Cells(i, 36).Value
.Cells(j, 32) = objWSPrice.Cells(i, 37).Value
.Cells(j, 33) = objWSPrice.Cells(i, 38).Value
.Cells(j, 34) = objWSPrice.Cells(i, 39).Value
.Cells(j, 35) = objWSPrice.Cells(i, 41).Value
.Cells(j, 36) = objWSPrice.Cells(i, 48).Value
.Cells(j, 37) = objWSPrice.Cells(i, 49).Value
.Cells(j, 38) = objWSPrice.Cells(i, 50).Value
.Cells(j, 39) = objWSPrice.Cells(i, 51).Value
.Cells(j, 40) = objWSPrice.Cells(i, 52).Value
.Cells(j, 41) = objWSPrice.Cells(i, 53).Value
.Cells(j, 42) = objWSPrice.Cells(i, 54).Value
.Cells(j, 43) = objWSPrice.Cells(i, 55).Value
.Cells(j, 44) = objWSPrice.Cells(i, 56).Value
.Cells(j, 45) = objWSPrice.Cells(i, 57).Value
.Cells(j, 46) = objWSPrice.Cells(i, 58).Value
.Cells(j, 47) = objWSPrice.Cells(i, 59).Value
.Cells(j, 48) = objWSPrice.Cells(i, 60).Value
.Cells(j, 49) = objWSPrice.Cells(i, 61).Value
.Cells(j, 50) = objWSPrice.Cells(i, 62).Value
.Cells(j, 51) = objWSPrice.Cells(i, 63).Value
.Cells(j, 52) = objWSPrice.Cells(i, 64).Value
.Cells(j, 53) = objWSPrice.Cells(i, 65).Value
.Cells(j, 54) = objWSPrice.Cells(i, 66).Value
.Cells(j, 55) = objWSPrice.Cells(i, 67).Value
.Cells(j, 56) = objWSPrice.Cells(i, 68).Value
.Cells(j, 57) = objWSPrice.Cells(i, 69).Value
.Cells(j, 58) = objWSPrice.Cells(i, 70).Value
.Cells(j, 59) = objWSPrice.Cells(i, 71).Value
.Cells(j, 60) = objWSPrice.Cells(i, 72).Value
.Cells(j, 61) = objWSPrice.Cells(i, 73).Value
.Cells(j, 62) = objWSPrice.Cells(i, 74).Value
.Cells(j, 63) = objWSPrice.Cells(i, 75).Value
.Cells(j, 64) = objWSPrice.Cells(i, 76).Value
.Cells(j, 65) = objWSPrice.Cells(i, 77).Value
.Cells(j, 66) = objWSPrice.Cells(i, 78).Value
.Cells(j, 67) = objWSPrice.Cells(i, 79).Value
.Cells(j, 68) = objWSPrice.Cells(i, 80).Value
.Cells(j, 69) = objWSPrice.Cells(i, 81).Value
.Cells(j, 70) = objWSPrice.Cells(i, 82).Value
.Cells(j, 71) = objWSPrice.Cells(i, 83).Value
.Cells(j, 72) = objWSPrice.Cells(i, 84).Value
.Cells(j, 73) = objWSPrice.Cells(i, 85).Value
.Cells(j, 74) = objWSPrice.Cells(i, 86).Value
.Cells(j, 75) = objWSPrice.Cells(i, 87).Value
.Cells(j, 76) = objWSPrice.Cells(i, 88).Value
.Cells(j, 77) = objWSPrice.Cells(i, 89).Value
j = j + 1
End With
End If
Next i
With objWSExport
.Visible = True
.Activate
.Copy
End With
Set objWBExport = ActiveWorkbook
strPath = objWBPrice.Path & Chr(92)
strFilename = strPath & Left(objWBPrice.Name, Len(objWBPrice.Name) - 4)
objWBExport.SaveAs Filename:=strFilename, FileFormat:=xlCSV
MsgBox "File Creation Complete", vbOKOnly, "CSV File"
objWSExport.Visible = False
End Sub

 

[Sam Wilson]

1. It clears the worksheet "CSV Export"
2. For each row in worksheet "Price" if the value in column B is "Y" some
cells from the row are copied into "CSV Export" (This is the long trail of
..Cells(j, 23) = objWSPrice.Cells(i, 28).Value lines)
3. Does a "Save As" calling the new (csv) file the same thing as the old
(xls) file

Sam

 

[MrRJ]

That is a good start Sam.

You mentioned that rows are copied into CSV Export. Can you explain how
many rows are captured or what does this mean exactly. Thought is was
selected columns? How does it now how many rows, what if I had 5,000? Would
it work?
..Cells(j, 5) = objWSPrice.Cells(i, 6).Value
Also, I noticed that I have to capital Y for it to work. Can I change the
code to reflect both lower and upper case Y?
How did you know it was column B? The letter Y is in column A.

I appreciate your help in my understanding this code.

 

[Sam Wilson]

Hi,

Ok - There's the following line: For i = 2 To objWSPrice.UsedRange.Rows.Count

This says from row 2 to the last used row in the worksheet "Price" if you've
used row 10,000 then it will know to go up to 10,000.

Next, "Y" and "y" - change 'If objWSPrice.Cells(i, 1).Value = "Y" Then' to
this :
If ucase(objWSPrice.Cells(i, 1).Value) = "Y" Then

I thought it was column B after not reading your code properly. Again, in
this:
If ucase(objWSPrice.Cells(i, 1).Value) = "Y" Then
cells(1,1) refers to A1, cells(2,1) refers to A2, cells(1,2) refers to B1
etc. It's like a grid reference. Cells(i,1) refers, because i is going from 2
to 10,000 (or however many rows are used in "Price" to cells A2, then A3,
then... then A10,000.

Sam

 

[MrRJ]

Sam,
You have been a great help! Couple more questions.
A) My interpretation of this line is this: Cell E1 (of the current file)
is on the new CSV file on cell F#. Is the correct? What is the point of
using the letter "j"?
.Cells(j, 5) = objWSPrice.Cells(i, 6).Value

B) What is the bottom part of the code really mean?
strPath = objWBPrice.Path & Chr(92)
strFilename = strPath & Left(objWBPrice.Name, Len(objWBPrice.Name) - 4)
objWSExport.Visible = False

C) I sometimes like to use F8 function (Step into) to watch the macro work
line by line. It won't work here, why? For me, watching it helps me
understand what each line does while it is running.

I truly appreciate this.
MrRJ

 

[Sam Wilson]

cells(j, 5) refers to column E but not cell E1 - j increases with i because
of the line "j=j+1" inside the For... Next loop. So Cells(j,5) referes to E1,
then E2, then E3...

For the bottom of the code I'm assuming PRICES is a sheet called Test.xls,
saved in C:\Folder1\Folder2

strPath = objWBPrice.Path & Chr(92) joins two strings together - the first
is the path of Test.xls so "C:\Folder1\Folder2" the second, Chr(92), is "\" -
so joining them together is "C:\Folder1\Folder2\"

Left(objWBPrice.Name, Len(objWBPrice.Name) - 4) takes all but the last 4
charcaters of the file name, "Test.xls" ie "Test" - this is then appended to
the path above to get "C:\Folder1\Folder2\Test" - the .csv is specified in
the line with "SaveAs"

objWSExport.Visible = False hides the saved .csv file from view.

F8 should work, but for a lot of the commands nothing visible happens.

If that all helps, mark it as the answer!

Sam

 

[Rick Rothstein]

It looks like Sam has addressed your direct questions. I just thought you
might be interested in shortening that code. All those assignments inside
the For..Next loop can be replaced with a single (addition) For..Next loop.
Consider this modification to the code you posted...

Sub CreateCSVFile()
Dim I As Long, J As Long, Z As Long, Index As Long
Dim objWBPrice As Workbook
Dim objWBExport As Workbook
Dim objWSPrice As Worksheet
Dim objWSExport As Worksheet
Set objWBPrice = ActiveWorkbook
Set objWSPrice = objWBPrice.Worksheets("Price")
Set objWSExport = objWBPrice.Worksheets("CSV Export")
With objWSExport.Cells
.Clear
With .Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 11
.ColorIndex = xlAutomatic
End With
End With
Application.ScreenUpdating = False
J = 1
For I = 2 To objWSPrice.UsedRange.Rows.Count
If objWSPrice.Cells(I, 1).Value = "Y" Then
For Z = 1 To 8
Index = Z + 1 - 2 * (Z >= 9) - 2 * _
(Z >= 10) - (Z >= 35) - 6 * (Z >= 36)
objWSExport.Cells(J, Z) = objWSPrice.Cells(I, Index).Value
Next
J = J + 1
End If
Next I
With objWSExport
.Visible = True
.Activate
.Copy
End With
Set objWBExport = ActiveWorkbook
strPath = objWBPrice.Path & "\"
strFilename = strPath & Left(objWBPrice.Name, Len(objWBPrice.Name) - 4)
objWBExport.SaveAs Filename:=strFilename, FileFormat:=xlCSV
MsgBox "File Creation Complete", vbOKOnly, "CSV File"
objWSExport.Visible = False
End Sub

Inside the new loop, the Index variable calculates the proper column number,
accounting for the "skipping over columns" that appears in your posted
sample. This is handled by the logical expressions within the Index variable
calculation. The reason for the minus signs is that logically True
expressions evaluate to -1 (minus one) in VB; so, in order to add the amount
(of column skipping) represented by the number being multiplied by the
logical expression, we need to subtract the negative result (that is the
same as adding a minus value times -1).