What Does This Code Do?
 

Can somebody explain what this section of code does? It is located i
Sheet1 of the VBA Explorer in Excel.

Here it is:


On Error GoTo errorhandler
If avoidloop And Trim(TARGET) < "" Then
If TARGET = "1" Then
Range("C2").Select
Application.SendKeys "{F2}"
Else
Select Case (ActiveCell.Column)
Case 1
avoidloop = False
If ActiveSheet.Rows(2).Columns(1).Value
TARGET Then
ActiveSheet.Rows(ActiveCell.Row - 1).Columns(1).Value
TARGET
ActiveSheet.Rows(ActiveCell.Row - 1).Columns(2).Value
""
avoidloop = True
Else
ActiveSheet.Rows(ActiveCell.Row - 1).Columns(2).Value
TARGET
ActiveSheet.Rows(ActiveCell.Row - 1).Columns(1).Value
""
avoidloop = True
End If
Case 2
Case 3
If TARGET < "" Then SAVE_DATA (TARGET)
Case Else
End Select
End If
End If
errorhandler:
End Su

--
Lil Pu
-----------------------------------------------------------------------
Lil Pun's Profile: http://www.excelforum.com/member.php...fo&userid=3384
View this thread: http://www.excelforum.com/showthread.php?threadid=53694

What Does This Code Do?
 

What part(s) don't you understand?

RBS


"Lil Pun" wrote in
message ...

Can somebody explain what this section of code does? It is located in
Sheet1 of the VBA Explorer in Excel.

Here it is:


On Error GoTo errorhandler
If avoidloop And Trim(TARGET) < "" Then
If TARGET = "1" Then
Range("C2").Select
Application.SendKeys "{F2}"
Else
Select Case (ActiveCell.Column)
Case 1
avoidloop = False
If ActiveSheet.Rows(2).Columns(1).Value =
TARGET Then
ActiveSheet.Rows(ActiveCell.Row - 1).Columns(1).Value =
TARGET
ActiveSheet.Rows(ActiveCell.Row - 1).Columns(2).Value =
""
avoidloop = True
Else
ActiveSheet.Rows(ActiveCell.Row - 1).Columns(2).Value =
TARGET
ActiveSheet.Rows(ActiveCell.Row - 1).Columns(1).Value =
""
avoidloop = True
End If
Case 2
Case 3
If TARGET < "" Then SAVE_DATA (TARGET)
Case Else
End Select
End If
End If
errorhandler:
End Sub


--
Lil Pun
------------------------------------------------------------------------
Lil Pun's Profile:
http://www.excelforum.com/member.php...o&userid=33840
View this thread: http://www.excelforum.com/showthread...hreadid=536944

What Does This Code Do?
 

All of it actually, the rest of the code in this file is commented so I
think if I understand what this part means I will understand how it
works with the rest of the other code in the project. I can post that
as well if needed or wanted. This code did have some commenting at the
top of the page which I will post at the end of this post. But oall the
other commenting basically goes line for line and these comments were at
the top of the page. Here are the comments:

' This routine is activated each time data from a cell is captured
' It needs to evaluate what changed and where it changed
' If it was in column A, B, C or any other; each column has a differen
process.
' Since this routine also changes data from others cells it is possible
that
' this routine calls itself. That is why the avoidloop is been used.
' When the routine knows that it is going to call itself it changes
this variable
' so that the complete routine can be jumped and ignored since it is
known what the data is
' and where it going to be placed.

And there was some code above those comments that have no comments:

Private Sub Worksheet_Activate()
avoidloop = True
End Sub

Private Sub Worksheet_Change(ByVal TARGET As Range)


I am just wanting to see how it all works together.


--
Lil Pun
------------------------------------------------------------------------
Lil Pun's Profile: http://www.excelforum.com/member.php...o&userid=33840
View this thread: http://www.excelforum.com/showthread...hreadid=536944

What Does This Code Do?
 

It is what does the things you said the application does.

If avoidloop And Trim(TARGET) < "" Then

first, it checks if AvoidLoop is True and the cell that triggered the code
(target) is not empty. If those cases check out then it checks:


If TARGET = "1" Then
Range("C2").Select
Application.SendKeys "{F2}"

Target is the cell that triggered the macro. If it has a value of 1 then
select cell C2 and go into edit mode.

if Target doesn't equal 1 then you choose from 3 possibilities




If the target is in column 1 then
if the target cell equals the value in A2 then
then in the row above the activecell
column A is set to the target value
column B is set to ""
else target doesn't equal A2 then
then in the row above the activecell
column A is set to ""
column B is set to the Target Value
end if


In each case, AvoidLoop is set to true

The second case is that the target is in column B
in that case, do nothing



the third case is if target is in column C
in that case, if Target is not empty, then executed SAVEDATA passing a
Target as a reference. (We wouldn't be here if Target were empty, so this
check is redundant).

If Target is not in columns A, B or C, then do nothing.


--
Regards,
Tom Ogilvy



"Lil Pun" wrote:


Can somebody explain what this section of code does? It is located in
Sheet1 of the VBA Explorer in Excel.

Here it is:


On Error GoTo errorhandler
If avoidloop And Trim(TARGET) < "" Then
If TARGET = "1" Then
Range("C2").Select
Application.SendKeys "{F2}"
Else
Select Case (ActiveCell.Column)
Case 1
avoidloop = False
If ActiveSheet.Rows(2).Columns(1).Value =
TARGET Then
ActiveSheet.Rows(ActiveCell.Row - 1).Columns(1).Value =
TARGET
ActiveSheet.Rows(ActiveCell.Row - 1).Columns(2).Value =
""
avoidloop = True
Else
ActiveSheet.Rows(ActiveCell.Row - 1).Columns(2).Value =
TARGET
ActiveSheet.Rows(ActiveCell.Row - 1).Columns(1).Value =
""
avoidloop = True
End If
Case 2
Case 3
If TARGET < "" Then SAVE_DATA (TARGET)
Case Else
End Select
End If
End If
errorhandler:
End Sub


--
Lil Pun
------------------------------------------------------------------------
Lil Pun's Profile: http://www.excelforum.com/member.php...o&userid=33840
View this thread: http://www.excelforum.com/showthread...hreadid=536944

What Does This Code Do?
 

OK, I think I am slowly but surely understanding the code but what do
those errorhandler variables do? As I said there are various pieces of
this file that I don't understand because it is not commented. I will
post all of the code in the file on this post.

Here is another piece of code, located in the Excel object
ThisWorkbook, what does this do:


Private Sub Workbook_Open()
Range("A2").Select
avoidloop = True
Application.SendKeys "{F2}"
End Sub



Here is some code located in Module1 but it looks to be commented well
except I would like to know more about the variables and what the code
next to the 'REM' is and means:


Global avoidloop As Boolean
Sub Macro1()
Range("A2").Select
End Sub
Sub SAVE_DATA(TARGET)
GoldenSheet = ActiveSheet.Name ' Saves the name of the sheet where
the data was captured
Sheets.Add ' Adds a new blank sheet to the
workbook
NewSheet = ActiveSheet.Name ' Obtains the name for the new
sheet


Sheets(GoldenSheet).Select ' Selects the sheet where the data
was captured
Columns("A:E").Select ' Selects the data from the sheet
where the data was captured
Selection.Copy ' Copies the data
Sheets(NewSheet).Select ' Selects the new blank created
sheet
ActiveSheet.Paste ' Pastes the data to this sheet


Rem For i = 1 To 100
Rem Sheets(NewSheet).Cells(i, 1) = Sheets(GoldenSheet).Cells(i,
1)
Rem Sheets(NewSheet).Cells(i, 2) = Sheets(GoldenSheet).Cells(i,
2)
Rem Sheets(NewSheet).Cells(i, 3) = Sheets(GoldenSheet).Cells(i,
3)
Rem Rem Sheets(NewSheet).Cells(i, 4) =
Sheets(GoldenSheet).Cells(i, 4)
Rem Rem Sheets(NewSheet).Cells(i, 5) =
Sheets(GoldenSheet).Cells(i, 5)
Rem Next i


'Creates the name of the file
FullPathFile = Trim(Sheets("Control").Cells(3, 3)) &
Trim(Sheets("Control").Cells(4, 3)) & Trim(TARGET) & "-" & Year(Now) &
"-" & Format(Month(Now), "00") & "-" & Format(Day(Now), "00") & ".xls"
increment = 1

' Verifies if the file exist
' If it exists the file name will change until it finds a name that
does not exists
Do While (Dir(FullPathFile) < "")
trim_ = InStr(FullPathFile, "_")
trimxls = InStr(FullPathFile, ".xls")
parcialpath = Left(FullPathFile, trimxls - 1)
If trim_ 1 Then
parcialpath = Left(parcialpath, trim_ - 1)
FullPathFile = parcialpath & "_" & increment & ".xls"
Else
FullPathFile = parcialpath & "_" & increment & ".xls"
End If
increment = increment + 1
Loop
Rem tempfilename = active

'Selects cell from the new sheet
Range("A2").Select
'Places the current time in correct cell
ActiveSheet.Cells(2, 4) = Hour(Now) & ":" & Format(Minute(Now),
"00") & ":" & Format(Second(Now), "00")
'Places the current date in correct cell
ActiveSheet.Cells(2, 5) = Year(Now) & "-" & Format(Month(Now),
"00") & "-" & Format(Day(Now), "00")
'Selects cell from new sheet
Range("A2").Select
'Moves the sheet to a new workbook and erases the new sheet from
the workbook that has the macro
ActiveWindow.SelectedSheets.Move
'Saves the file/workbook
ActiveWorkbook.SaveAs Filename:=FullPathFile, FileFormat:=xlNormal,
_
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
'Closes the file/workbook
ActiveWorkbook.Close
Rem ActiveWorkbook.Close
avoidloop = False
' Automatically returns the control to the workbook that has the
macro

' Erases all the data captured from the first 100 rows of data
For i = 2 To 100
Sheets(GoldenSheet).Cells(i, 1) = ""
Sheets(GoldenSheet).Cells(i, 2) = ""
Sheets(GoldenSheet).Cells(i, 3) = ""
Next i
avoidloop = True

' If the checkbox for the confirmation of saved file is checked, a
message box will appear
If Sheets("Control").CheckBox1.Value Then MsgBox "File " &
FullPathFile & " Created"

' Selects the cell A2 to start a new data entry
Range("A2").Select

' Sends the F2 key to put cell in edit mode
Application.SendKeys "{F2}"


End Sub

Sub TransferLocation()

'Macro inserts transfer directory name from control button

Location = Application.GetOpenFilename("All files (*.*), *.*")

If Location < False Then
FindSeparator = InStr(Location, "\")
Do While FindSeparator
GetPath = Left(Location, FindSeparator)
FindSeparator = InStr(FindSeparator + 1, Location, "\")
Loop
EXPORTCONTROL.Cells(3, 3) = Trim(GetPath) 'Displays only
path
Rem EXPORTCONTROL.Cells(3, 3) = Location 'Displays full name
& path
End If
Rem namesheets (True)
End Sub


And this was the code that was just explained to me with comments
that were already listed:

Private Sub Worksheet_Activate()
avoidloop = True
End Sub

Private Sub Worksheet_Change(ByVal TARGET As Range)
' This routine is activated each time data from a cell is captured
' It needs to evaluate what changed and where it changed
' If it was in column A, B, C or any other; each column has a differen
process.
' Since this routine also changes data from others cells it is possible
that
' this routine calls itself. That is why the avoidloop is been used.
' When the routine knows that it is going to call itself it changes
this variable
' so that the complete routine can be jumped and ignored since it is
known what the data is
' and where it going to be placed.

On Error GoTo errorhandler
If avoidloop And Trim(TARGET) < "" Then
If TARGET = "1" Then
Range("C2").Select
Application.SendKeys "{F2}"
Else
Select Case (ActiveCell.Column)
Case 1
avoidloop = False
If ActiveSheet.Rows(2).Columns(1).Value =
TARGET Then
ActiveSheet.Rows(ActiveCell.Row -
1).Columns(1).Value = TARGET
ActiveSheet.Rows(ActiveCell.Row -
1).Columns(2).Value = ""
avoidloop = True
Else
ActiveSheet.Rows(ActiveCell.Row -
1).Columns(2).Value = TARGET
ActiveSheet.Rows(ActiveCell.Row -
1).Columns(1).Value = ""
avoidloop = True
End If
Case 2
Case 3
If TARGET < "" Then SAVE_DATA (TARGET)
Case Else
End Select
End If
End If
errorhandler:
End Sub




--
Lil Pun
------------------------------------------------------------------------
Lil Pun's Profile: http://www.excelforum.com/member.php...o&userid=33840
View this thread: http://www.excelforum.com/showthread...hreadid=536944

What Does This Code Do?
 

The error handler just jumps to the end of the sub if there is an error
rather than stopping the code and putting up an error message.

--
Regards,
Tom Ogilvy


"Lil Pun" wrote:


OK, I think I am slowly but surely understanding the code but what do
those errorhandler variables do? As I said there are various pieces of
this file that I don't understand because it is not commented. I will
post all of the code in the file on this post.

Here is another piece of code, located in the Excel object
ThisWorkbook, what does this do:


Private Sub Workbook_Open()
Range("A2").Select
avoidloop = True
Application.SendKeys "{F2}"
End Sub



Here is some code located in Module1 but it looks to be commented well
except I would like to know more about the variables and what the code
next to the 'REM' is and means:


Global avoidloop As Boolean
Sub Macro1()
Range("A2").Select
End Sub
Sub SAVE_DATA(TARGET)
GoldenSheet = ActiveSheet.Name ' Saves the name of the sheet where
the data was captured
Sheets.Add ' Adds a new blank sheet to the
workbook
NewSheet = ActiveSheet.Name ' Obtains the name for the new
sheet


Sheets(GoldenSheet).Select ' Selects the sheet where the data
was captured
Columns("A:E").Select ' Selects the data from the sheet
where the data was captured
Selection.Copy ' Copies the data
Sheets(NewSheet).Select ' Selects the new blank created
sheet
ActiveSheet.Paste ' Pastes the data to this sheet


Rem For i = 1 To 100
Rem Sheets(NewSheet).Cells(i, 1) = Sheets(GoldenSheet).Cells(i,
1)
Rem Sheets(NewSheet).Cells(i, 2) = Sheets(GoldenSheet).Cells(i,
2)
Rem Sheets(NewSheet).Cells(i, 3) = Sheets(GoldenSheet).Cells(i,
3)
Rem Rem Sheets(NewSheet).Cells(i, 4) =
Sheets(GoldenSheet).Cells(i, 4)
Rem Rem Sheets(NewSheet).Cells(i, 5) =
Sheets(GoldenSheet).Cells(i, 5)
Rem Next i


'Creates the name of the file
FullPathFile = Trim(Sheets("Control").Cells(3, 3)) &
Trim(Sheets("Control").Cells(4, 3)) & Trim(TARGET) & "-" & Year(Now) &
"-" & Format(Month(Now), "00") & "-" & Format(Day(Now), "00") & ".xls"
increment = 1

' Verifies if the file exist
' If it exists the file name will change until it finds a name that
does not exists
Do While (Dir(FullPathFile) < "")
trim_ = InStr(FullPathFile, "_")
trimxls = InStr(FullPathFile, ".xls")
parcialpath = Left(FullPathFile, trimxls - 1)
If trim_ 1 Then
parcialpath = Left(parcialpath, trim_ - 1)
FullPathFile = parcialpath & "_" & increment & ".xls"
Else
FullPathFile = parcialpath & "_" & increment & ".xls"
End If
increment = increment + 1
Loop
Rem tempfilename = active

'Selects cell from the new sheet
Range("A2").Select
'Places the current time in correct cell
ActiveSheet.Cells(2, 4) = Hour(Now) & ":" & Format(Minute(Now),
"00") & ":" & Format(Second(Now), "00")
'Places the current date in correct cell
ActiveSheet.Cells(2, 5) = Year(Now) & "-" & Format(Month(Now),
"00") & "-" & Format(Day(Now), "00")
'Selects cell from new sheet
Range("A2").Select
'Moves the sheet to a new workbook and erases the new sheet from
the workbook that has the macro
ActiveWindow.SelectedSheets.Move
'Saves the file/workbook
ActiveWorkbook.SaveAs Filename:=FullPathFile, FileFormat:=xlNormal,
_
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
'Closes the file/workbook
ActiveWorkbook.Close
Rem ActiveWorkbook.Close
avoidloop = False
' Automatically returns the control to the workbook that has the
macro

' Erases all the data captured from the first 100 rows of data
For i = 2 To 100
Sheets(GoldenSheet).Cells(i, 1) = ""
Sheets(GoldenSheet).Cells(i, 2) = ""
Sheets(GoldenSheet).Cells(i, 3) = ""
Next i
avoidloop = True

' If the checkbox for the confirmation of saved file is checked, a
message box will appear
If Sheets("Control").CheckBox1.Value Then MsgBox "File " &
FullPathFile & " Created"

' Selects the cell A2 to start a new data entry
Range("A2").Select

' Sends the F2 key to put cell in edit mode
Application.SendKeys "{F2}"


End Sub

Sub TransferLocation()

'Macro inserts transfer directory name from control button

Location = Application.GetOpenFilename("All files (*.*), *.*")

If Location < False Then
FindSeparator = InStr(Location, "\")
Do While FindSeparator
GetPath = Left(Location, FindSeparator)
FindSeparator = InStr(FindSeparator + 1, Location, "\")
Loop
EXPORTCONTROL.Cells(3, 3) = Trim(GetPath) 'Displays only
path
Rem EXPORTCONTROL.Cells(3, 3) = Location 'Displays full name
& path
End If
Rem namesheets (True)
End Sub


And this was the code that was just explained to me with comments
that were already listed:

Private Sub Worksheet_Activate()
avoidloop = True
End Sub

Private Sub Worksheet_Change(ByVal TARGET As Range)
' This routine is activated each time data from a cell is captured
' It needs to evaluate what changed and where it changed
' If it was in column A, B, C or any other; each column has a differen
process.
' Since this routine also changes data from others cells it is possible
that
' this routine calls itself. That is why the avoidloop is been used.
' When the routine knows that it is going to call itself it changes
this variable
' so that the complete routine can be jumped and ignored since it is
known what the data is
' and where it going to be placed.

On Error GoTo errorhandler
If avoidloop And Trim(TARGET) < "" Then
If TARGET = "1" Then
Range("C2").Select
Application.SendKeys "{F2}"
Else
Select Case (ActiveCell.Column)
Case 1
avoidloop = False
If ActiveSheet.Rows(2).Columns(1).Value =
TARGET Then
ActiveSheet.Rows(ActiveCell.Row -
1).Columns(1).Value = TARGET
ActiveSheet.Rows(ActiveCell.Row -
1).Columns(2).Value = ""
avoidloop = True
Else
ActiveSheet.Rows(ActiveCell.Row -
1).Columns(2).Value = TARGET
ActiveSheet.Rows(ActiveCell.Row -
1).Columns(1).Value = ""
avoidloop = True
End If
Case 2
Case 3
If TARGET < "" Then SAVE_DATA (TARGET)
Case Else
End Select
End If
End If
errorhandler:
End Sub




--
Lil Pun
------------------------------------------------------------------------
Lil Pun's Profile: http://www.excelforum.com/member.php...o&userid=33840
View this thread: http://www.excelforum.com/showthread...hreadid=536944

What Does This Code Do?
 

What is avoidloop in the above code and what does it do?


--
Lil Pun
------------------------------------------------------------------------
Lil Pun's Profile: http://www.excelforum.com/member.php...o&userid=33840
View this thread: http://www.excelforum.com/showthread...hreadid=536944

What Does This Code Do?
 

What "above code"?

Please *quote* relevant content and remember we're not all using "web forums" (yuk!).

--
Tim Williams
Palo Alto, CA


"Lil Pun" wrote in message
...

What is avoidloop in the above code and what does it do?


--
Lil Pun
------------------------------------------------------------------------
Lil Pun's Profile: http://www.excelforum.com/member.php...o&userid=33840
View this thread: http://www.excelforum.com/showthread...hreadid=536944