Solved: Code Help Needed

[YellowLabPro]

The following code has been altered originally by a board member. I am using this as my initial kickoff for developing a new function. With no experience, I am trying to understand VBA through code written by others and then modify it to fit my needs.

Would someone edit this to do the following tasks? Or start a new file. I will use it to compare between the two for learning purposes.
Copy these particular columns from worksheet TGFF to worksheet Data; Columns A, C, D, E, M, and AP starting in Column B. I would need the term Fairfax to be inserted in Column A and copy down as far as the number of records that will be populated from the TGFF worksheet.





Option Explicit
'
Sub FairfaxItemRecords()
'
Dim FirstAddress As String, Cell As Range
'
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'
'========================
With Sheets("TGFF").Columns("E")
Set Cell = .Find(What:="~~P", LookIn:=xlValues, LookAt:=xlPart)
'
If Not Cell Is Nothing Then
'
FirstAddress = Cell.Address
'
Do
With Sheets("ParentData").Range("A" & Rows.Count).End(xlUp)
.Offset(1, 0) = Cell.Offset(0, -4)
.Offset(1, 1) = Cell
.Offset(1, 2) = Cell
End With
Set Cell = .FindNext(Cell)
Loop Until Cell Is Nothing Or Cell.Address = FirstAddress
'
End If
'
'---------------------------------------------
'
Set Cell = .Find(What:="~~C", LookIn:=xlValues, LookAt:=xlPart)
'
If Not Cell Is Nothing Then
'
FirstAddress = Cell.Address
'
Do
With Sheets("ChildData").Range("A" & Rows.Count).End(xlUp)
.Offset(1, 0) = Cell.Offset(0, -4)
.Offset(1, 1) = Cell
.Offset(1, 2) = Cell
End With
Set Cell = .FindNext(Cell)
Loop Until Cell Is Nothing Or Cell.Address = FirstAddress
'
End If
End With
'
'========================
'
With Sheets("TGVB").Columns("E")
Set Cell = .Find(What:="~~P", LookIn:=xlValues, LookAt:=xlPart)
'
If Not Cell Is Nothing Then
'
FirstAddress = Cell.Address
'
Do
With Sheets("ParentData").Range("A" & Rows.Count).End(xlUp)
.Offset(1, 0) = Cell.Offset(0, -4)
.Offset(1, 1) = Cell
.Offset(1, 2) = Cell
End With
Set Cell = .FindNext(Cell)
Loop Until Cell Is Nothing Or Cell.Address = FirstAddress
End If
'
'---------------------------------------------
'
Set Cell = .Find(What:="~~C", LookIn:=xlValues, LookAt:=xlPart)
'
If Not Cell Is Nothing Then
'
FirstAddress = Cell.Address
'
Do
With Sheets("ChildData").Range("A" & Rows.Count).End(xlUp)
.Offset(1, 0) = Cell.Offset(0, -4)
.Offset(1, 1) = Cell
.Offset(1, 2) = Cell
End With
Set Cell = .FindNext(Cell)
Loop Until Cell Is Nothing Or Cell.Address = FirstAddress
'
End If
End With
'========================
'
'
With Sheets("ParentData")
.Columns(3).Replace What:="~~P", Replacement:="", MatchCase:=False
For Each Cell In .Range("C2", Range("C" & Rows.Count).End(xlUp).Address)
Cell = Trim(Cell)
Next
.Range("A1") = "Parent Item#"
.Range("B1") = "Parent Item Description"
.Range("C1") = "Parent Trimmed"
End With
'
'---------------------------------------------
'
With Sheets("ChildData")
.Columns(3).Replace What:="~~C", Replacement:="", MatchCase:=False
For Each Cell In .Range("C2", Range("C" & Rows.Count).End(xlUp).Address)
Cell = Trim(Cell)
Next
.Range("A1") = "Child Item#"
.Range("B1") = "Child Item Description"
.Range("C1") = "Child Trimmed"
End With
'
'---------------------------------------------
'
Set Cell = Nothing
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
'
End Sub



Thanks,

YLP

[mdmackillop]

Hi YLP
Here's some code for you. It uses an array to store the source column letters (adjust to suit your needs) and an incrementing variable "i" to set the destination. SpecialCells is used to determine the last row containing data, and this value is used in the Range address where Fairfax is to be entered. In this case it is not neceesary to copy down.
Regards
MD


Option Explicit
Sub FairFax()
Dim Cols, Col
Dim i As Long
i = 2
Cols = Array("B", "D", "F")
With Worksheets("TGFF")
'Enter copied columns into successive columns
For Each Col In Cols
.Columns(Col).Copy Sheets("Data").Cells(1, i)
i = i + 1
Next
End With
'Use SpecialCells to determine the last row used on Data
With Worksheets("Data")
.Range(Cells(1, 1), Cells(.Cells.SpecialCells(xlCellTypeLastCell).Row, 1)) = "Fairfax"
End With
End Sub

[YellowLabPro]

MD,
Very nice. Could you do some deciphering for me? I am having difficulty finding basic info for entering the world of VBA.
1. Dim: I have found it to be defined as Dimension. But that is about all I have found. You have used this to set something for the procedure, what is its purpose?
2. "i" is set for long and then set for the value of "2", why?
3. I see that you have commented to pastes the cells in succession,

[YellowLabPro]

MD,
Very nice. Could you do some deciphering for me? I am having difficulty finding basic info for entering the world of VBA.

1. Dim: I have found it to be defined as Dimension. But that is about all I have found. You have used this to set something for the procedure, what is its purpose?
2. "i" is set for long and then set for the value of "2", why?
3. I see that you have commented to pastes the cells in succession,
i) What is telling it to actually paste?
ii) What is this line of code doing: .Columns(Col).Copy Sheets("Data").Cells(1, i)
i = i + 1
4. The term "Fairfax" is being inserted in R1C1 or A1, the data is placed in R2C2 or B2, there is a mismatch. Where in the code and what code would need to be altered to place the data in R2C1 or B1, where and what in the code is instructing where to place "Fairfax"?

Thanks for your assistance and time,

YLP

[mdmackillop]

Hi YLP,
1. Dim basically serves 2 purposes.
It tells the programme the memory space required, for efficiency
It limits the type of data that can be assigned to the variable to the correct "type", helping to avoid programming errors.
When the Option Explicit statement is used, you must declare all variables with a Dim statement.
2. i is going to be a column number. It could have been declared Integer, but at the system converts integers to Long, I used Long (less typing as well!). 2 is the column number for "B" where the data is to be pasted. It is simplier to increment numbers trhan letters.
3. i) See Copy Method in VBA Help. The location after the copy keyword is the destination.
ii) The enclosing With statement sets the sheet from which the data will be copied
.Columns(Col).Copy - Col is B, D and F in turn. The "." preceding Columns ties this to the sheet named in the With statement, otherwise the Activesheet (which may differ) could be copied.

Sheets("Data").Cells(1, i) - This is the destination. Where i = 2, the represents B1 on Sheet "Data". Incrementing i changes this to C1 and D1

Without looping, this would be equivalent to:

Worksheets("TGFF").Columns("B").Copy Destination:= WorkSheets("Data").Range("B1")
Worksheets("TGFF").Columns("D").Copy Destination:= WorkSheets("Data").Range("C1")
Worksheets("TGFF").Columns("F").Copy Destination:= WorkSheets("Data").Range("D1")

4. The last section of code in simple terms would be something like
WorkSheets("Data").Range("A1:A8")="Fairfax"
The use of the Cells notation allows calulated values to be more easily inserted into the code (although it may look more confusing)

Initially, you may wish to calculate variables, then use them in the Cell notation. If I were using a value more than once, I would do it that way.

e.g.

Dim LastRow as long
LastRow = Worksheets("Data").Cells.SpecialCells(xlCellTypeLastCell).Row
MsgBox LastRow " is the last row used"
With Worksheets("Data")
.Range(Cells(1, 1), Cells(LastRow, 1)) = "Fairfax"
End With

[YellowLabPro]

Thanks MD.