loop

[Daphne Eze]

Excel 2010 Win 7


I have been trying to teach myself VBA. I am somewhat familiar with
programming but I can't figure this out. Can someone point me in the
right direction?

Thanks
Daphne

I have a spreadsheet with information about members of a club.

I need to copy some of the information to a new workbook depending on
the value of cell D in each row.

For example
if the value of cell A in row 1 is "T" then copy cells B1 to E1 to cell
B1 of the first empty row in a new workbook

but if the value is "F" then go to the next row and repeat the routine
until an empty row is found.

A B C D E

 

[Mike S]

Excel 2010 Win 7


I have been trying to teach myself VBA. I am somewhat familiar with
programming but I can't figure this out. Can someone point me in the
right direction?

Thanks
Daphne

I have a spreadsheet with information about members of a club.

I need to copy some of the information to a new workbook depending on
the value of cell D in each row.

For example
if the value of cell A in row 1 is "T" then copy cells B1 to E1 to cell
B1 of the first empty row in a new workbook

but if the value is "F" then go to the next row and repeat the routine
until an empty row is found.

A B C D E

Here are some pieces of example code that might help to get you started
with the basics, I'm sure other people know a lot more about Excel here
but this will probably get you close to what you need.

For starters, do you know how to select a file or open a file from a
file browse dialog? Do either of these help to select the target
workbook where the data will be copied?

http://www.exceltip.com/st/Select_filenames_using_VBA_in_Microsoft_Excel/448.html

Public Function FindFile() As String
'open browse window to select target workbook where data will be copied
Dim fn As String
fn = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select
File To Copy Data To", , False)
If fn = "" Then
MsgBox "Nothing Chosen"
Else
'now that you have the name, you can open it or do something else
'return filename
FindFile = fn
'show user filename they selected (just for testing
MsgBox "You selected the file """ & fn & """", vbOKOnly,
"Target Workbook"
End If
End Function

Sub OpenOneSelectedExcelFile()
Dim fn As Variant
fn = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select
One File To Open", , False)
If TypeName(fn) = "Boolean" Then
' the user didn't select a file
Exit Sub
End If
Debug.Print "Selected file: " & fn
Workbooks.Open fn
End Sub
'--------------------------------------------------------------------------

http://en.allexperts.com/q/Excel-1059/2008/9/Macro-copy-data-workbook.htm

This code example allow you to select a second worksheet from an opened
worksheet that contains this code, and copy data from the first to the
selected sheets.

Public Function FindFile() As String
'browse to select target workbook where data will be copied
'right now it only shows files with the xls file extension
Dim fn As String
fn = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select
File To Copy Data To", , False)
If fn = "" Then
exit function
Else
'return filename
FindFile = fn
End If
End Function

Public Sub CopyDataToTargetBook()
'declare variables
Dim sh1 As Worksheet, sh2 As Worksheet, bk2 As Workbook
Dim row As Long, col As Long
'--------------------------------------------------------
'refer to sheet in open book data will be copied from
Set sh1 = ActiveSheet
Application.ScreenUpdating = False
'--------------------------------------------------------
'open sheet data will be copied to
Set bk2 = Workbooks.Open(FindFile)
'target sheet where data will be copied
Set sh2 = bk2.Sheets(1)
'arbitrary copy code example, not using your conditions
For row = 1 To 8
For col = 1 To 3
sh2.Cells(row, col).Value = sh1.Cells(row, col)
Next
Next
'save the book with the copied data
bk2.Close Savechanges:=True
Application.ScreenUpdating = True
'---------------------------------------------------------
set sh1=nothing
set sh2=nothing
set bk2=nothing
End Sub

Does that get you started?
Mike

 

[Daphne Eze]

I'm sorry. I don't think I was clear enough on what I needed. I know how
to open or create another workbook or worksheet.

What I don't know how to do is find out what the value of cell "A1" is
and then if the value is "T" copy the row to a new workbook or worksheet

But if the value of cell "A1" is "F" skip that row and move down a row

and continue the process until all the rows with data in them have been
processed.

 

[Mike S]

I'm sorry. I don't think I was clear enough on what I needed. I know how
to open or create another workbook or worksheet.

The first code example lets you select another worksheet that already
exists.

What I don't know how to do is find out what the value of cell "A1" is
and then if the value is "T" copy the row to a new workbook or worksheet

The CopyDataToTargetBook code example shows you how to read and copy the
contents of a cell.

But if the value of cell "A1" is "F" skip that row and move down a row
and continue the process until all the rows with data in them have been
processed.

That will be easy once you get all of the basics in place.

Do you know how to enter code and run it? Do you know how to step
through it one line at at time so you can watch what's happening very
closely? And do you know how to use the debug.print immediate window?

Mike

 

[Andrew]

I'm sorry. I don't think I was clear enough on what I needed. I know how
to open or create another workbook or worksheet.

What I don't know how to do is find out what the value of cell "A1" is
and then if the value is "T" copy the row to a new workbook or worksheet

But if the value of cell "A1" is "F" skip that row and move down a row

and continue the process until all the rows with data in them have been
processed.

Here is how I would do it. I am sort of old school and I like loops.
There are many better ways to do this, but it seems easier to
understand using fundamental programming constructs. Once you have
the concept down, you can make it as fancy as you want. This code
copies from one sheet called "data" to another sheet called "file".
You can modify this to copy anywhere.

' First find out how many rows are filled with data in your sheet
' Once you run this you will know your last row of data
For k = 1 To 10000
If IsEmpty(Cells(k, 1)) = True Then
endrow = k - 1
Exit For
End If
Next
Cells(10, 10) = endrow ' this is put a value on the sheet so you can
verify that the code has actually found the last row of data

' Now that you have endrow, you can check the values of each cell is
column 1 to see if it equals "T"
row_data = 0 ' row_data will be used for copying to the proper row
on the next sheet

For k = 1 To endrow ' this covers all data in the table
If Cells(k, 1) = "T" Then ' check to see if cell value = "T"
row_data = row_data + 1 'if it does, increment rwo_data
Sheets("file").Cells(row_data, 1) = Sheets("data").Cells(k, 2) ' copy
over cells. The copied cells will be in row k, the destination row
will be row_data
Sheets("file").Cells(row_data, 2) = Sheets("data").Cells(k, 3)
Sheets("file").Cells(row_data, 3) = Sheets("data").Cells(k, 4)
End If
Next

 

[Mike S]

Here is how I would do it. I am sort of old school and I like loops.
There are many better ways to do this, but it seems easier to
understand using fundamental programming constructs. Once you have
the concept down, you can make it as fancy as you want. This code
copies from one sheet called "data" to another sheet called "file".
You can modify this to copy anywhere.

' First find out how many rows are filled with data in your sheet
' Once you run this you will know your last row of data
For k = 1 To 10000
If IsEmpty(Cells(k, 1)) = True Then
endrow = k - 1
Exit For
End If
Next
Cells(10, 10) = endrow ' this is put a value on the sheet so you can
verify that the code has actually found the last row of data

' Now that you have endrow, you can check the values of each cell is
column 1 to see if it equals "T"
row_data = 0 ' row_data will be used for copying to the proper row
on the next sheet

For k = 1 To endrow ' this covers all data in the table
If Cells(k, 1) = "T" Then ' check to see if cell value = "T"
row_data = row_data + 1 'if it does, increment rwo_data
Sheets("file").Cells(row_data, 1) = Sheets("data").Cells(k, 2) ' copy
over cells. The copied cells will be in row k, the destination row
will be row_data
Sheets("file").Cells(row_data, 2) = Sheets("data").Cells(k, 3)
Sheets("file").Cells(row_data, 3) = Sheets("data").Cells(k, 4)
End If
Next

That looks great! I think you're right, I was approaching it wrong and
making it seem harder. Good job.

 

[Daphne Eze]

Thanks you Andrew and Mike. This is what I wanted.

I'm working my way thro' the code. Because I'm just learning VBA I need
to look up all the codes to understand what each one does and how it is
used.

I will probably have more questions.

Cheers
Daphne

 

[Andrew]

Thanks you Andrew and Mike. This is what I wanted.

I'm working my way thro' the code. Because I'm just learning VBA I need
to look up all the codes to understand what each one does and how it is
used.

I will probably have more questions.

Cheers
Daphne

Daphne, one good way to understand this (or any) code is to run it in
monitor mode. You can do this by going to the VBA code and then
pressing F8. Each press of F8 and the code will increment itself by
one line of instruction. After each line is executed, you can hover
your mouse over the program variables. Their values will change as
the program runs. This way you can watch how the code executes step by
step.

 

[Daphne Eze]

Daphne, one good way to understand this (or any) code is to run it in
monitor mode. You can do this by going to the VBA code and then
pressing F8. Each press of F8 and the code will increment itself by
one line of instruction. After each line is executed, you can hover
your mouse over the program variables. Their values will change as
the program runs. This way you can watch how the code executes step by
step.

Great Thank you Andrew.