| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find data in Column D, add L in Column A
Hello All,
I have a sheet that in Column A the default value will be L for those rows that contain information. I'm creating a macro so would like to put a code in there that will put an L in Column A if there is data in Column D. Example Column A ColumnD L MICROTEST L LECTRATEST L SAMPLE Since total rows will change on a monthly basis I can't have a specific range. Would appreciate the help. Thanks, Juan |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find data in Column D, add L in Column A
Hi
try the following macro: Sub process_rows() Dim lastrow As Long Dim row_index As Long Application.ScreenUpdating = False lastrow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row For row_index = 1 to lastrow If Cells(row_index, 4).Value <"" and _ cells(row_index,1).value="" then cells(row_index,1).value="L" End If Next Application.ScreenUpdating = True End Sub -- Regards Frank Kabel Frankfurt, Germany "juan" schrieb im Newsbeitrag ... Hello All, I have a sheet that in Column A the default value will be L for those rows that contain information. I'm creating a macro so would like to put a code in there that will put an L in Column A if there is data in Column D. Example Column A ColumnD L MICROTEST L LECTRATEST L SAMPLE Since total rows will change on a monthly basis I can't have a specific range. Would appreciate the help. Thanks, Juan |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find data in Column D, add L in Column A
Hello Frank,
this is great seems to be working as a charm. One last thing. At end of last record entered, I put a summary( Represents an S) and total of how many records it contains. Example, L TEST L TEST2 S2 Is there w way I can put S in Column A after last record? and in Column B the total count of records? I can do it manually but since doing a macro trying to have less manual input. Would appreciate if this can be done. But THANK YOU So Much for getting back to me this fast YOu have helped me in the past and Really Appreciate it ALOT. THANKS, JUAN -----Original Message----- Hi try the following macro: Sub process_rows() Dim lastrow As Long Dim row_index As Long Application.ScreenUpdating = False lastrow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row For row_index = 1 to lastrow If Cells(row_index, 4).Value <"" and _ cells(row_index,1).value="" then cells(row_index,1).value="L" End If Next Application.ScreenUpdating = True End Sub -- Regards Frank Kabel Frankfurt, Germany "juan" schrieb im Newsbeitrag ... Hello All, I have a sheet that in Column A the default value will be L for those rows that contain information. I'm creating a macro so would like to put a code in there that will put an L in Column A if there is data in Column D. Example Column A ColumnD L MICROTEST L LECTRATEST L SAMPLE Since total rows will change on a monthly basis I can't have a specific range. Would appreciate the help. Thanks, Juan . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find data in Column D, add L in Column A
Hi
try: Sub process_rows() Dim lastrow As Long Dim row_index As Long Application.ScreenUpdating = False lastrow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row For row_index = 1 to lastrow If Cells(row_index, 4).Value <"" and _ cells(row_index,1).value="" then cells(row_index,1).value="L" End If Next with cells(lastrow+1,1) .value="S" .offset(0,1).formulaR1C1="=COUNTIF(R1C1:R[-1]C1,""L"")" end with Application.ScreenUpdating = True End Sub -- Regards Frank Kabel Frankfurt, Germany "Juan" schrieb im Newsbeitrag ... Hello Frank, this is great seems to be working as a charm. One last thing. At end of last record entered, I put a summary( Represents an S) and total of how many records it contains. Example, L TEST L TEST2 S2 Is there w way I can put S in Column A after last record? and in Column B the total count of records? I can do it manually but since doing a macro trying to have less manual input. Would appreciate if this can be done. But THANK YOU So Much for getting back to me this fast YOu have helped me in the past and Really Appreciate it ALOT. THANKS, JUAN -----Original Message----- Hi try the following macro: Sub process_rows() Dim lastrow As Long Dim row_index As Long Application.ScreenUpdating = False lastrow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row For row_index = 1 to lastrow If Cells(row_index, 4).Value <"" and _ cells(row_index,1).value="" then cells(row_index,1).value="L" End If Next Application.ScreenUpdating = True End Sub -- Regards Frank Kabel Frankfurt, Germany "juan" schrieb im Newsbeitrag ... Hello All, I have a sheet that in Column A the default value will be L for those rows that contain information. I'm creating a macro so would like to put a code in there that will put an L in Column A if there is data in Column D. Example Column A ColumnD L MICROTEST L LECTRATEST L SAMPLE Since total rows will change on a monthly basis I can't have a specific range. Would appreciate the help. Thanks, Juan . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find data in Column D, add L in Column A
Hello Frank,
thanks for the extra help. FYI, the formula doesn't seem to be working. It just puts in Column B =COUNTIF(R1C1:R[-1]C1,"L") I'll play around with this and see if I can make it to work. If not I'll just have users manually put the total records. I really appreciate all your help. thanks again, juan -----Original Message----- Hi try: Sub process_rows() Dim lastrow As Long Dim row_index As Long Application.ScreenUpdating = False lastrow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row For row_index = 1 to lastrow If Cells(row_index, 4).Value <"" and _ cells(row_index,1).value="" then cells(row_index,1).value="L" End If Next with cells(lastrow+1,1) .value="S" .offset(0,1).formulaR1C1="=COUNTIF(R1C1:R[-1] C1,""L"")" end with Application.ScreenUpdating = True End Sub -- Regards Frank Kabel Frankfurt, Germany "Juan" schrieb im Newsbeitrag ... Hello Frank, this is great seems to be working as a charm. One last thing. At end of last record entered, I put a summary( Represents an S) and total of how many records it contains. Example, L TEST L TEST2 S2 Is there w way I can put S in Column A after last record? and in Column B the total count of records? I can do it manually but since doing a macro trying to have less manual input. Would appreciate if this can be done. But THANK YOU So Much for getting back to me this fast YOu have helped me in the past and Really Appreciate it ALOT. THANKS, JUAN -----Original Message----- Hi try the following macro: Sub process_rows() Dim lastrow As Long Dim row_index As Long Application.ScreenUpdating = False lastrow = ActiveSheet.Cells(Rows.Count, 4).End (xlUp).Row For row_index = 1 to lastrow If Cells(row_index, 4).Value <"" and _ cells(row_index,1).value="" then cells(row_index,1).value="L" End If Next Application.ScreenUpdating = True End Sub -- Regards Frank Kabel Frankfurt, Germany "juan" schrieb im Newsbeitrag ... Hello All, I have a sheet that in Column A the default value will be L for those rows that contain information. I'm creating a macro so would like to put a code in there that will put an L in Column A if there is data in Column D. Example Column A ColumnD L MICROTEST L LECTRATEST L SAMPLE Since total rows will change on a monthly basis I can't have a specific range. Would appreciate the help. Thanks, Juan . . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find data in Column D, add L in Column A
Hi
try changing the format of this column first to 'General'. Maybe the cell is formated as 'Text' -- Regards Frank Kabel Frankfurt, Germany "juan" schrieb im Newsbeitrag ... Hello Frank, thanks for the extra help. FYI, the formula doesn't seem to be working. It just puts in Column B =COUNTIF(R1C1:R[-1]C1,"L") I'll play around with this and see if I can make it to work. If not I'll just have users manually put the total records. I really appreciate all your help. thanks again, juan -----Original Message----- Hi try: Sub process_rows() Dim lastrow As Long Dim row_index As Long Application.ScreenUpdating = False lastrow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row For row_index = 1 to lastrow If Cells(row_index, 4).Value <"" and _ cells(row_index,1).value="" then cells(row_index,1).value="L" End If Next with cells(lastrow+1,1) .value="S" .offset(0,1).formulaR1C1="=COUNTIF(R1C1:R[-1] C1,""L"")" end with Application.ScreenUpdating = True End Sub -- Regards Frank Kabel Frankfurt, Germany "Juan" schrieb im Newsbeitrag ... Hello Frank, this is great seems to be working as a charm. One last thing. At end of last record entered, I put a summary( Represents an S) and total of how many records it contains. Example, L TEST L TEST2 S2 Is there w way I can put S in Column A after last record? and in Column B the total count of records? I can do it manually but since doing a macro trying to have less manual input. Would appreciate if this can be done. But THANK YOU So Much for getting back to me this fast YOu have helped me in the past and Really Appreciate it ALOT. THANKS, JUAN -----Original Message----- Hi try the following macro: Sub process_rows() Dim lastrow As Long Dim row_index As Long Application.ScreenUpdating = False lastrow = ActiveSheet.Cells(Rows.Count, 4).End (xlUp).Row For row_index = 1 to lastrow If Cells(row_index, 4).Value <"" and _ cells(row_index,1).value="" then cells(row_index,1).value="L" End If Next Application.ScreenUpdating = True End Sub -- Regards Frank Kabel Frankfurt, Germany "juan" schrieb im Newsbeitrag ... Hello All, I have a sheet that in Column A the default value will be L for those rows that contain information. I'm creating a macro so would like to put a code in there that will put an L in Column A if there is data in Column D. Example Column A ColumnD L MICROTEST L LECTRATEST L SAMPLE Since total rows will change on a monthly basis I can't have a specific range. Would appreciate the help. Thanks, Juan . . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find data in Column D, add L in Column A
Hello Frank,
getting close LOL. I did change the column to General and run the macro and it puts #NAME? but if I go into the cell and put cursor at end of formula and then hit enter, it gives me the right number. So there as to be something minor wrong in the macro code? Here's what I have: Sub ADDRECINFO() Dim lastrow As Long Dim row_index As Long Application.ScreenUpdating = False lastrow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row For row_index = 1 To lastrow If Cells(row_index, 4).Value < "" And _ Cells(row_index, 1).Value = "" Then Cells(row_index, 1).Value = "L" Cells(row_index, 2).Value = "MNLA" Cells(row_index, 7).Value = "PH" Cells(row_index, 14).Value = "USD" End If Next With Cells(lastrow + 1, 1) .Value = "S" .Offset(0, 1).FormulaR1C1 = "=COUNTIF(A:A,""L"")" End With Application.ScreenUpdating = True End Sub This is part of the complete macro I'm doing. Thanks for the help juan -----Original Message----- Hi try changing the format of this column first to 'General'. Maybe the cell is formated as 'Text' -- Regards Frank Kabel Frankfurt, Germany "juan" schrieb im Newsbeitrag ... Hello Frank, thanks for the extra help. FYI, the formula doesn't seem to be working. It just puts in Column B =COUNTIF(R1C1:R[-1]C1,"L") I'll play around with this and see if I can make it to work. If not I'll just have users manually put the total records. I really appreciate all your help. thanks again, juan -----Original Message----- Hi try: Sub process_rows() Dim lastrow As Long Dim row_index As Long Application.ScreenUpdating = False lastrow = ActiveSheet.Cells(Rows.Count, 4).End (xlUp).Row For row_index = 1 to lastrow If Cells(row_index, 4).Value <"" and _ cells(row_index,1).value="" then cells(row_index,1).value="L" End If Next with cells(lastrow+1,1) .value="S" .offset(0,1).formulaR1C1="=COUNTIF(R1C1:R[-1] C1,""L"")" end with Application.ScreenUpdating = True End Sub -- Regards Frank Kabel Frankfurt, Germany "Juan" schrieb im Newsbeitrag ... Hello Frank, this is great seems to be working as a charm. One last thing. At end of last record entered, I put a summary ( Represents an S) and total of how many records it contains. Example, L TEST L TEST2 S2 Is there w way I can put S in Column A after last record? and in Column B the total count of records? I can do it manually but since doing a macro trying to have less manual input. Would appreciate if this can be done. But THANK YOU So Much for getting back to me this fast YOu have helped me in the past and Really Appreciate it ALOT. THANKS, JUAN -----Original Message----- Hi try the following macro: Sub process_rows() Dim lastrow As Long Dim row_index As Long Application.ScreenUpdating = False lastrow = ActiveSheet.Cells(Rows.Count, 4).End (xlUp).Row For row_index = 1 to lastrow If Cells(row_index, 4).Value <"" and _ cells(row_index,1).value="" then cells(row_index,1).value="L" End If Next Application.ScreenUpdating = True End Sub -- Regards Frank Kabel Frankfurt, Germany "juan" schrieb im Newsbeitrag ... Hello All, I have a sheet that in Column A the default value will be L for those rows that contain information. I'm creating a macro so would like to put a code in there that will put an L in Column A if there is data in Column D. Example Column A ColumnD L MICROTEST L LECTRATEST L SAMPLE Since total rows will change on a monthly basis I can't have a specific range. Would appreciate the help. Thanks, Juan . . . |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find data in Column D, add L in Column A
Hello Frank,
just wanted to let you know, I figured it out and now it seems to work. So thank you once again for all your great help. Have a good one. Juan -----Original Message----- Hi try changing the format of this column first to 'General'. Maybe the cell is formated as 'Text' -- Regards Frank Kabel Frankfurt, Germany "juan" schrieb im Newsbeitrag ... Hello Frank, thanks for the extra help. FYI, the formula doesn't seem to be working. It just puts in Column B =COUNTIF(R1C1:R[-1]C1,"L") I'll play around with this and see if I can make it to work. If not I'll just have users manually put the total records. I really appreciate all your help. thanks again, juan -----Original Message----- Hi try: Sub process_rows() Dim lastrow As Long Dim row_index As Long Application.ScreenUpdating = False lastrow = ActiveSheet.Cells(Rows.Count, 4).End (xlUp).Row For row_index = 1 to lastrow If Cells(row_index, 4).Value <"" and _ cells(row_index,1).value="" then cells(row_index,1).value="L" End If Next with cells(lastrow+1,1) .value="S" .offset(0,1).formulaR1C1="=COUNTIF(R1C1:R[-1] C1,""L"")" end with Application.ScreenUpdating = True End Sub -- Regards Frank Kabel Frankfurt, Germany "Juan" schrieb im Newsbeitrag ... Hello Frank, this is great seems to be working as a charm. One last thing. At end of last record entered, I put a summary ( Represents an S) and total of how many records it contains. Example, L TEST L TEST2 S2 Is there w way I can put S in Column A after last record? and in Column B the total count of records? I can do it manually but since doing a macro trying to have less manual input. Would appreciate if this can be done. But THANK YOU So Much for getting back to me this fast YOu have helped me in the past and Really Appreciate it ALOT. THANKS, JUAN -----Original Message----- Hi try the following macro: Sub process_rows() Dim lastrow As Long Dim row_index As Long Application.ScreenUpdating = False lastrow = ActiveSheet.Cells(Rows.Count, 4).End (xlUp).Row For row_index = 1 to lastrow If Cells(row_index, 4).Value <"" and _ cells(row_index,1).value="" then cells(row_index,1).value="L" End If Next Application.ScreenUpdating = True End Sub -- Regards Frank Kabel Frankfurt, Germany "juan" schrieb im Newsbeitrag ... Hello All, I have a sheet that in Column A the default value will be L for those rows that contain information. I'm creating a macro so would like to put a code in there that will put an L in Column A if there is data in Column D. Example Column A ColumnD L MICROTEST L LECTRATEST L SAMPLE Since total rows will change on a monthly basis I can't have a specific range. Would appreciate the help. Thanks, Juan . . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| FInd common data in one column then add number in adjacent column | Excel Worksheet Functions | |||
| compare data in column A with column B to find duplicates | Excel Discussion (Misc queries) | |||
| find the data of the corresponding column | New Users to Excel | |||
| Find something in column a then find if column B matches criteria | Excel Discussion (Misc queries) | |||
| Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
Linear Mode
