Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Correct Macro so that if the person is not in the source document
Hi:
This is my macro that I need to fix....If the person does not have any data on the "Daily Attendance" worksheet, I want it to skip that person's data on their named worksheet....In other words, if Craig doesn't have anything on Daily Attendance, I want it to go to the next worksheet and put the correct data in for that person.....I hope this makes sense.......Any help would be appreciated...... Sheets("Daily Attendance").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n If Cells(i, "A").Value = "Lavendar, Craig" Then v = Cells(i, "C").Value Exit For End If Next Sheets("Craig L").Activate Range("N21").Value = v Sheets("Daily Attendance").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n If Cells(i, "A").Value = "Blackmon, Jack" Then v = Cells(i, "C").Value Exit For End If Next -- jeannie v |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Correct Macro so that if the person is not in the source document
jeannie,
Here are two versions of the edited macro that do what you want. 1) Here you check against each name and if present in the Daily Attendance sheet, then you update n21 on their named sheet. Note however, that there is some redundancy in the code, not to mention that if you have a lot of names, then you would have to type quit a few if statements. Public Sub FillNames() Sheets("Daily Attendance").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n With Cells(i, "A") If .Value = "name1" Then Sheets(.Value).Range("n21").Value = Cells(i, "C").Value ElseIf .Value = "name2" Then Sheets(.Value).Range("n21").Value = Cells(i, "C").Value ElseIf .Value = "name3" Then Sheets(.Value).Range("n21").Value = Cells(i, "C").Value End If End With Next End Sub thus, choice 2 which is shorter and does not require that you enter an if for each name. In this version you must include all the names in the attendance sheet, by leave the cell in column c empty. Public Sub FillNames() Dim lastRow As Integer With Sheets("Daily Attendance") lastRow = .Cells(Rows.Count, "A").End(xlUp).Row End With For i = 1 To lastRow With Sheets("Daily Attendance").Cells(i, "C") If Not IsEmpty(.Value) Then Sheets(.Offset(0, -2).Value).Range("n21").Value = .Value End If End With Next End Sub HTH. Cheers, Socratis "jeannie v" wrote: Hi: This is my macro that I need to fix....If the person does not have any data on the "Daily Attendance" worksheet, I want it to skip that person's data on their named worksheet....In other words, if Craig doesn't have anything on Daily Attendance, I want it to go to the next worksheet and put the correct data in for that person.....I hope this makes sense.......Any help would be appreciated...... Sheets("Daily Attendance").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n If Cells(i, "A").Value = "Lavendar, Craig" Then v = Cells(i, "C").Value Exit For End If Next Sheets("Craig L").Activate Range("N21").Value = v Sheets("Daily Attendance").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n If Cells(i, "A").Value = "Blackmon, Jack" Then v = Cells(i, "C").Value Exit For End If Next -- jeannie v |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Correct Macro so that if the person is not in the source docum
Good Morning, Socratis:
Thank you so much for your help......I will try both versions and see which is the best for my use.....I will let you know if I have any problems since I'm so new at macros. I can't thank you enough for lending your talents and expertise. -- jeannie v "Socratis" wrote: jeannie, Here are two versions of the edited macro that do what you want. 1) Here you check against each name and if present in the Daily Attendance sheet, then you update n21 on their named sheet. Note however, that there is some redundancy in the code, not to mention that if you have a lot of names, then you would have to type quit a few if statements. Public Sub FillNames() Sheets("Daily Attendance").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n With Cells(i, "A") If .Value = "name1" Then Sheets(.Value).Range("n21").Value = Cells(i, "C").Value ElseIf .Value = "name2" Then Sheets(.Value).Range("n21").Value = Cells(i, "C").Value ElseIf .Value = "name3" Then Sheets(.Value).Range("n21").Value = Cells(i, "C").Value End If End With Next End Sub thus, choice 2 which is shorter and does not require that you enter an if for each name. In this version you must include all the names in the attendance sheet, by leave the cell in column c empty. Public Sub FillNames() Dim lastRow As Integer With Sheets("Daily Attendance") lastRow = .Cells(Rows.Count, "A").End(xlUp).Row End With For i = 1 To lastRow With Sheets("Daily Attendance").Cells(i, "C") If Not IsEmpty(.Value) Then Sheets(.Offset(0, -2).Value).Range("n21").Value = .Value End If End With Next End Sub HTH. Cheers, Socratis "jeannie v" wrote: Hi: This is my macro that I need to fix....If the person does not have any data on the "Daily Attendance" worksheet, I want it to skip that person's data on their named worksheet....In other words, if Craig doesn't have anything on Daily Attendance, I want it to go to the next worksheet and put the correct data in for that person.....I hope this makes sense.......Any help would be appreciated...... Sheets("Daily Attendance").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n If Cells(i, "A").Value = "Lavendar, Craig" Then v = Cells(i, "C").Value Exit For End If Next Sheets("Craig L").Activate Range("N21").Value = v Sheets("Daily Attendance").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n If Cells(i, "A").Value = "Blackmon, Jack" Then v = Cells(i, "C").Value Exit For End If Next -- jeannie v |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Correct Macro so that if the person is not in the source docum
Hi Socratis:
I have a problem....it won't pop the value in "C" on the Named Sheet.....I'm using the first option you suggested since the 2nd option won't work....C will always have a value and cannot be empty...the data is pulled at once from another app and planted on the Daily Attendance sheet. This is exactly what I did to modify the macro you recommended....could you tell me what I'm dong wrong? I have checked and rechecked every naming convention on the Daily Attendance sheet to be sure they are correct. When I run the macro, I get an error message : Run Time Error 9 Subscript out of range.....Remember that I am really new at macros and I know that you are extremely sophisticated with them.......Thank you so much for all your help and your patience.... Public Sub FillNames() Sheets("Daily Attendance").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n With Cells(i, "A") If .Value = "Perez, Arnaldis" Then Sheets(.Value).Range("n21").Value = Cells(i, "C").Value ElseIf .Value = "Johnson1, David" Then Sheets(.Value).Range("n21").Value = Cells(i, "C").Value ElseIf .Value = "Diaz 1, Henry" Then Sheets(.Value).Range("n21").Value = Cells(i, "C").Value ElseIf .Value = "Lafleur, Kimberlyn" Then Sheets(.Value).Range("n21").Value = Cells(i, "C").Value ElseIf .Value = "Wyatt1, Mariya" Then Sheets(.Value).Range("n21").Value = Cells(i, "C").Value ElseIf .Value = "Gonzalez, Merita" Then Sheets(.Value).Range("n21").Value = Cells(i, "C").Value ElseIf .Value = "Moyers1, Ricardo" Then Sheets(.Value).Range("n21").Value = Cells(i, "C").Value ElseIf .Value = "Kelly, Robert" Then Sheets(.Value).Range("n21").Value = Cells(i, "C").Value End If End With Next End Sub -- jeannie v "Socratis" wrote: jeannie, Here are two versions of the edited macro that do what you want. 1) Here you check against each name and if present in the Daily Attendance sheet, then you update n21 on their named sheet. Note however, that there is some redundancy in the code, not to mention that if you have a lot of names, then you would have to type quit a few if statements. Public Sub FillNames() Sheets("Daily Attendance").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n With Cells(i, "A") If .Value = "name1" Then Sheets(.Value).Range("n21").Value = Cells(i, "C").Value ElseIf .Value = "name2" Then Sheets(.Value).Range("n21").Value = Cells(i, "C").Value ElseIf .Value = "name3" Then Sheets(.Value).Range("n21").Value = Cells(i, "C").Value End If End With Next End Sub thus, choice 2 which is shorter and does not require that you enter an if for each name. In this version you must include all the names in the attendance sheet, by leave the cell in column c empty. Public Sub FillNames() Dim lastRow As Integer With Sheets("Daily Attendance") lastRow = .Cells(Rows.Count, "A").End(xlUp).Row End With For i = 1 To lastRow With Sheets("Daily Attendance").Cells(i, "C") If Not IsEmpty(.Value) Then Sheets(.Offset(0, -2).Value).Range("n21").Value = .Value End If End With Next End Sub HTH. Cheers, Socratis "jeannie v" wrote: Hi: This is my macro that I need to fix....If the person does not have any data on the "Daily Attendance" worksheet, I want it to skip that person's data on their named worksheet....In other words, if Craig doesn't have anything on Daily Attendance, I want it to go to the next worksheet and put the correct data in for that person.....I hope this makes sense.......Any help would be appreciated...... Sheets("Daily Attendance").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n If Cells(i, "A").Value = "Lavendar, Craig" Then v = Cells(i, "C").Value Exit For End If Next Sheets("Craig L").Activate Range("N21").Value = v Sheets("Daily Attendance").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n If Cells(i, "A").Value = "Blackmon, Jack" Then v = Cells(i, "C").Value Exit For End If Next -- jeannie v |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Correct Macro so that if the person is not in the source docum
One other thing: the Sheets Names or not the same naming convention as the
Daily Attendance sheet....i.e. Perez, Arnaldis on Daily Attendance is AJ P on the Sheets Name. Is this the problem? -- jeannie v "Socratis" wrote: jeannie, Here are two versions of the edited macro that do what you want. 1) Here you check against each name and if present in the Daily Attendance sheet, then you update n21 on their named sheet. Note however, that there is some redundancy in the code, not to mention that if you have a lot of names, then you would have to type quit a few if statements. Public Sub FillNames() Sheets("Daily Attendance").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n With Cells(i, "A") If .Value = "name1" Then Sheets(.Value).Range("n21").Value = Cells(i, "C").Value ElseIf .Value = "name2" Then Sheets(.Value).Range("n21").Value = Cells(i, "C").Value ElseIf .Value = "name3" Then Sheets(.Value).Range("n21").Value = Cells(i, "C").Value End If End With Next End Sub thus, choice 2 which is shorter and does not require that you enter an if for each name. In this version you must include all the names in the attendance sheet, by leave the cell in column c empty. Public Sub FillNames() Dim lastRow As Integer With Sheets("Daily Attendance") lastRow = .Cells(Rows.Count, "A").End(xlUp).Row End With For i = 1 To lastRow With Sheets("Daily Attendance").Cells(i, "C") If Not IsEmpty(.Value) Then Sheets(.Offset(0, -2).Value).Range("n21").Value = .Value End If End With Next End Sub HTH. Cheers, Socratis "jeannie v" wrote: Hi: This is my macro that I need to fix....If the person does not have any data on the "Daily Attendance" worksheet, I want it to skip that person's data on their named worksheet....In other words, if Craig doesn't have anything on Daily Attendance, I want it to go to the next worksheet and put the correct data in for that person.....I hope this makes sense.......Any help would be appreciated...... Sheets("Daily Attendance").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n If Cells(i, "A").Value = "Lavendar, Craig" Then v = Cells(i, "C").Value Exit For End If Next Sheets("Craig L").Activate Range("N21").Value = v Sheets("Daily Attendance").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n If Cells(i, "A").Value = "Blackmon, Jack" Then v = Cells(i, "C").Value Exit For End If Next -- jeannie v |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Correct Macro so that if the person is not in the source docum
Good Morning, Socratis:
I figured it out and your macro worked! Thank you so much for all your help...I am thrilled that it is what I want. Best Regards, -- jeannie v "Socratis" wrote: jeannie, Here are two versions of the edited macro that do what you want. 1) Here you check against each name and if present in the Daily Attendance sheet, then you update n21 on their named sheet. Note however, that there is some redundancy in the code, not to mention that if you have a lot of names, then you would have to type quit a few if statements. Public Sub FillNames() Sheets("Daily Attendance").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n With Cells(i, "A") If .Value = "name1" Then Sheets(.Value).Range("n21").Value = Cells(i, "C").Value ElseIf .Value = "name2" Then Sheets(.Value).Range("n21").Value = Cells(i, "C").Value ElseIf .Value = "name3" Then Sheets(.Value).Range("n21").Value = Cells(i, "C").Value End If End With Next End Sub thus, choice 2 which is shorter and does not require that you enter an if for each name. In this version you must include all the names in the attendance sheet, by leave the cell in column c empty. Public Sub FillNames() Dim lastRow As Integer With Sheets("Daily Attendance") lastRow = .Cells(Rows.Count, "A").End(xlUp).Row End With For i = 1 To lastRow With Sheets("Daily Attendance").Cells(i, "C") If Not IsEmpty(.Value) Then Sheets(.Offset(0, -2).Value).Range("n21").Value = .Value End If End With Next End Sub HTH. Cheers, Socratis "jeannie v" wrote: Hi: This is my macro that I need to fix....If the person does not have any data on the "Daily Attendance" worksheet, I want it to skip that person's data on their named worksheet....In other words, if Craig doesn't have anything on Daily Attendance, I want it to go to the next worksheet and put the correct data in for that person.....I hope this makes sense.......Any help would be appreciated...... Sheets("Daily Attendance").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n If Cells(i, "A").Value = "Lavendar, Craig" Then v = Cells(i, "C").Value Exit For End If Next Sheets("Craig L").Activate Range("N21").Value = v Sheets("Daily Attendance").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n If Cells(i, "A").Value = "Blackmon, Jack" Then v = Cells(i, "C").Value Exit For End If Next -- jeannie v |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Correct Macro so that if the person is not in the source docum
Hi Socratis:
I have another macro that is presenting a problem and I have tried several people for help but they are making it really complicated and the macros don't work....The problem is: I want the macros to look at Column A on Worksheet 2 and select all incidence of a particular specialist's login info in Columns A through L and then copy that data to named Worksheet 1 in Cells A4 through L17. The problem with the below macro is that the first instance pops in A4 through L4, but the rest of the incidents pop below in lines 40 something.... Can you help me? Sub LoginLogoutMarquez() ' ' LoginLogoutMarquez Macro ' Macro recorded 6/23/2007 by Jeannie Vincovich ' ' Keyboard Shortcut: Ctrl+w ' Dim n As Integer, i As Integer, j As Integer Dim rng As Range j = 0 Sheets("Agent Login-Logout").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n Sheets("Agent Login-Logout").Activate If Cells(i, "A").Value = "Diaz 1, Henry" Then Range("A" & i & ":L" & i).Copy Sheets("Henry D").Activate If j = 0 Then Set rng = Worksheets("Henry D").Range("A4") rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False j = j + 1 Else n = Cells(Rows.Count, "A").End(xlUp).Row + 1 Set rng = Worksheets("Henry D").Range("A" & n) rng.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If End If Next i End Sub -- jeannie v "Socratis" wrote: jeannie, Here are two versions of the edited macro that do what you want. 1) Here you check against each name and if present in the Daily Attendance sheet, then you update n21 on their named sheet. Note however, that there is some redundancy in the code, not to mention that if you have a lot of names, then you would have to type quit a few if statements. Public Sub FillNames() Sheets("Daily Attendance").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n With Cells(i, "A") If .Value = "name1" Then Sheets(.Value).Range("n21").Value = Cells(i, "C").Value ElseIf .Value = "name2" Then Sheets(.Value).Range("n21").Value = Cells(i, "C").Value ElseIf .Value = "name3" Then Sheets(.Value).Range("n21").Value = Cells(i, "C").Value End If End With Next End Sub thus, choice 2 which is shorter and does not require that you enter an if for each name. In this version you must include all the names in the attendance sheet, by leave the cell in column c empty. Public Sub FillNames() Dim lastRow As Integer With Sheets("Daily Attendance") lastRow = .Cells(Rows.Count, "A").End(xlUp).Row End With For i = 1 To lastRow With Sheets("Daily Attendance").Cells(i, "C") If Not IsEmpty(.Value) Then Sheets(.Offset(0, -2).Value).Range("n21").Value = .Value End If End With Next End Sub HTH. Cheers, Socratis "jeannie v" wrote: Hi: This is my macro that I need to fix....If the person does not have any data on the "Daily Attendance" worksheet, I want it to skip that person's data on their named worksheet....In other words, if Craig doesn't have anything on Daily Attendance, I want it to go to the next worksheet and put the correct data in for that person.....I hope this makes sense.......Any help would be appreciated...... Sheets("Daily Attendance").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n If Cells(i, "A").Value = "Lavendar, Craig" Then v = Cells(i, "C").Value Exit For End If Next Sheets("Craig L").Activate Range("N21").Value = v Sheets("Daily Attendance").Activate n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n If Cells(i, "A").Value = "Blackmon, Jack" Then v = Cells(i, "C").Value Exit For End If Next -- jeannie v |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
source document changes | Excel Discussion (Misc queries) | |||
need to correct scanned document all i get is jumbled words | Excel Discussion (Misc queries) | |||
How can you see the name of the person who has a shared document . | Excel Discussion (Misc queries) | |||
Drawing info from another source document | Excel Discussion (Misc queries) | |||
convert existing Excel into Data source document to link to exist. | Excel Discussion (Misc queries) |