| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Interpretation
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code Interpretation
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" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code Interpretation
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" wrote: 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" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code Interpretation
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" wrote: 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" wrote: 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" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code Interpretation
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code Interpretation
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 "MrRJ" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code Interpretation
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). -- Rick (MVP - Excel) "MrRJ" wrote in message ... 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Interpretation of a piece of code | Excel Discussion (Misc queries) | |||
| Interpretation | Excel Discussion (Misc queries) | |||
| Code Interpretation | Excel Programming | |||
| Need interpretation | Excel Programming | |||
| value interpretation | Excel Programming | |||
Hybrid Mode
