Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro on cells that contain formulas
I have three columns, A, B, C. In columns A & B are dates. Data starts on
row 2, headers are in row 1. In column C I have a formula that says, =IF(COUNTIF($A$2:$A$16,B2)0,B2,"") This formula returns all the dates in my range that match. The problem is that the formula looks row by row and if a date in column B does not match a date in column A, it leaves a blank in the corresponding row of column C. I want to write a macro that deletes all blanks in column C, but column C is never "truly" empty because there is the formula in it. Can I delete my blank rows based on the blanks in column C with the formula existing? Can I use VB to do the work that my formula is doing? If so, how? Furthermore, if the date shows up more than once in column B, it still matches it to the first finding in Column A and I end up with two of the same dates in column C. Ex: 08-oct-04 How can I avoid this? Here's an example of my columns: Column A | Column B | Column C | 06-oct-04 12-oct-04 12-oct-04 07-oct-04 08-oct-04 08-oct-04 08-oct-04 03-oct-04 09-oct-04 04-oct-04 10-oct-04 05-oct-04 11-oct-04 06-oct-04 06-oct-04 12-oct-04 07-oct-04 07-oct-04 13-oct-04 08-oct-04 08-oct-04 I realize that if I delete rows 3, 4 & 5 (in this example) that I will lose the data in columns A & B. That's okay. All I really care about is column C. I hope this is clear and not confusing. Thanks for any help. Sharon |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro on cells that contain formulas
Sub BuildC()
Dim rngB as Range, rngA as Range Dim cellB as Range, rw as Long set rngB = Range(Cells(2,2),Cells(2,2).End(xldown)) set rngA = Range(Cells(2,1),Cells(2,1).End(xldown)) rw = 2 for each cellB in rngB if application.Countif(rngA,cellB)0 then cells(rw,3).Value = cellB cells(rw,3).NumberFormat = cellB.Numberformat rw = rw + 1 end if Next End Sub -- Regards, Tom Ogilvy "Sharon" wrote in message ... I have three columns, A, B, C. In columns A & B are dates. Data starts on row 2, headers are in row 1. In column C I have a formula that says, =IF(COUNTIF($A$2:$A$16,B2)0,B2,"") This formula returns all the dates in my range that match. The problem is that the formula looks row by row and if a date in column B does not match a date in column A, it leaves a blank in the corresponding row of column C. I want to write a macro that deletes all blanks in column C, but column C is never "truly" empty because there is the formula in it. Can I delete my blank rows based on the blanks in column C with the formula existing? Can I use VB to do the work that my formula is doing? If so, how? Furthermore, if the date shows up more than once in column B, it still matches it to the first finding in Column A and I end up with two of the same dates in column C. Ex: 08-oct-04 How can I avoid this? Here's an example of my columns: Column A | Column B | Column C | 06-oct-04 12-oct-04 12-oct-04 07-oct-04 08-oct-04 08-oct-04 08-oct-04 03-oct-04 09-oct-04 04-oct-04 10-oct-04 05-oct-04 11-oct-04 06-oct-04 06-oct-04 12-oct-04 07-oct-04 07-oct-04 13-oct-04 08-oct-04 08-oct-04 I realize that if I delete rows 3, 4 & 5 (in this example) that I will lose the data in columns A & B. That's okay. All I really care about is column C. I hope this is clear and not confusing. Thanks for any help. Sharon |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro on cells that contain formulas
Thanks for the code. I was hoping that I could figure out how to modify what
you've told me so far on my own, but alas, my inexperience keeps holding me back. This is what I was trying to figure out how to do without bothering anyone again: Column A | Column B | Column C | Column D | Column E | Column F | Column G 04-oct-04 green yellow 12-oct-04 yellow red 08-oct-04 yellow red 13-oct-04 red red 12-oct-04 green red 14-oct-04 green green 13-oct-04 green yellow 15-oct-04 red red With the code you gave me, I would get return values of: 12-oct-04 13-oct-04 which is exactly what I wanted. But now I also want to return the text in column F & G. In my real workbook, I am using R3:999C1:17 and R3:999C19:35 (vs. A2:16 in my original example). I did figure out how to modify the code you gave me to return the results on Sheet2, but I can only run the macro if I Sheet1 is the active sheet. 1) What would I add to the code so that the macro would run on Sheet2? 2) How do I expand the code so that the values are returned including Columns E-G and not just Column E? Here's your code that I modified to display the results on Sheet2: Sub CombineDates() Dim rngB As Range, rngA As Range Dim cellB As Range, rw As Long Set rngB = Range(Cells(3, 19), Cells(3, 19).End(xlDown)) Set rngA = Range(Cells(3, 1), Cells(3, 1).End(xlDown)) rw = 2 For Each cellB In rngB If Application.CountIf(rngA, cellB) 0 Then Sheet2.Cells(rw, 1).Value = cellB Sheet2.Cells(rw, 1).NumberFormat = cellB.NumberFormat rw = rw + 1 End If Next End Sub Thanks again for any help, Sharon "Tom Ogilvy" wrote: Sub BuildC() Dim rngB as Range, rngA as Range Dim cellB as Range, rw as Long set rngB = Range(Cells(2,2),Cells(2,2).End(xldown)) set rngA = Range(Cells(2,1),Cells(2,1).End(xldown)) rw = 2 for each cellB in rngB if application.Countif(rngA,cellB)0 then cells(rw,3).Value = cellB cells(rw,3).NumberFormat = cellB.Numberformat rw = rw + 1 end if Next End Sub -- Regards, Tom Ogilvy "Sharon" wrote in message ... I have three columns, A, B, C. In columns A & B are dates. Data starts on row 2, headers are in row 1. In column C I have a formula that says, =IF(COUNTIF($A$2:$A$16,B2)0,B2,"") This formula returns all the dates in my range that match. The problem is that the formula looks row by row and if a date in column B does not match a date in column A, it leaves a blank in the corresponding row of column C. I want to write a macro that deletes all blanks in column C, but column C is never "truly" empty because there is the formula in it. Can I delete my blank rows based on the blanks in column C with the formula existing? Can I use VB to do the work that my formula is doing? If so, how? Furthermore, if the date shows up more than once in column B, it still matches it to the first finding in Column A and I end up with two of the same dates in column C. Ex: 08-oct-04 How can I avoid this? Here's an example of my columns: Column A | Column B | Column C | 06-oct-04 12-oct-04 12-oct-04 07-oct-04 08-oct-04 08-oct-04 08-oct-04 03-oct-04 09-oct-04 04-oct-04 10-oct-04 05-oct-04 11-oct-04 06-oct-04 06-oct-04 12-oct-04 07-oct-04 07-oct-04 13-oct-04 08-oct-04 08-oct-04 I realize that if I delete rows 3, 4 & 5 (in this example) that I will lose the data in columns A & B. That's okay. All I really care about is column C. I hope this is clear and not confusing. Thanks for any help. Sharon |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro on cells that contain formulas
Sub CombineDates()
Dim rngB As Range, rngA As Range Dim cellB As Range, rw As Long With worksheets("Sheet1") Set rngB = .Range(.Cells(3, 19), .Cells(3, 19).End(xlDown)) Set rngA = .Range(.Cells(3, 1), .Cells(3, 1).End(xlDown)) rw = 2 For Each cellB In rngB If Application.CountIf(rngA, cellB) 0 Then Sheet2.Cells(rw, 1).Value = cellB Sheet2.Cells(rw, 1).NumberFormat = cellB.NumberFormat sheet2.Cells(rw,2).Value = .Cells(cellB.row,"F").Value sheet2.Cells(rw,3).Value = .Cells(cellB.row,"G").Value rw = rw + 1 End If Next End With End Sub -- Regards, Tom Ogilvy "Sharon" wrote in message ... Thanks for the code. I was hoping that I could figure out how to modify what you've told me so far on my own, but alas, my inexperience keeps holding me back. This is what I was trying to figure out how to do without bothering anyone again: Column A | Column B | Column C | Column D | Column E | Column F | Column G 04-oct-04 green yellow 12-oct-04 yellow red 08-oct-04 yellow red 13-oct-04 red red 12-oct-04 green red 14-oct-04 green green 13-oct-04 green yellow 15-oct-04 red red With the code you gave me, I would get return values of: 12-oct-04 13-oct-04 which is exactly what I wanted. But now I also want to return the text in column F & G. In my real workbook, I am using R3:999C1:17 and R3:999C19:35 (vs. A2:16 in my original example). I did figure out how to modify the code you gave me to return the results on Sheet2, but I can only run the macro if I Sheet1 is the active sheet. 1) What would I add to the code so that the macro would run on Sheet2? 2) How do I expand the code so that the values are returned including Columns E-G and not just Column E? Here's your code that I modified to display the results on Sheet2: Sub CombineDates() Dim rngB As Range, rngA As Range Dim cellB As Range, rw As Long Set rngB = Range(Cells(3, 19), Cells(3, 19).End(xlDown)) Set rngA = Range(Cells(3, 1), Cells(3, 1).End(xlDown)) rw = 2 For Each cellB In rngB If Application.CountIf(rngA, cellB) 0 Then Sheet2.Cells(rw, 1).Value = cellB Sheet2.Cells(rw, 1).NumberFormat = cellB.NumberFormat rw = rw + 1 End If Next End Sub Thanks again for any help, Sharon "Tom Ogilvy" wrote: Sub BuildC() Dim rngB as Range, rngA as Range Dim cellB as Range, rw as Long set rngB = Range(Cells(2,2),Cells(2,2).End(xldown)) set rngA = Range(Cells(2,1),Cells(2,1).End(xldown)) rw = 2 for each cellB in rngB if application.Countif(rngA,cellB)0 then cells(rw,3).Value = cellB cells(rw,3).NumberFormat = cellB.Numberformat rw = rw + 1 end if Next End Sub -- Regards, Tom Ogilvy "Sharon" wrote in message ... I have three columns, A, B, C. In columns A & B are dates. Data starts on row 2, headers are in row 1. In column C I have a formula that says, =IF(COUNTIF($A$2:$A$16,B2)0,B2,"") This formula returns all the dates in my range that match. The problem is that the formula looks row by row and if a date in column B does not match a date in column A, it leaves a blank in the corresponding row of column C. I want to write a macro that deletes all blanks in column C, but column C is never "truly" empty because there is the formula in it. Can I delete my blank rows based on the blanks in column C with the formula existing? Can I use VB to do the work that my formula is doing? If so, how? Furthermore, if the date shows up more than once in column B, it still matches it to the first finding in Column A and I end up with two of the same dates in column C. Ex: 08-oct-04 How can I avoid this? Here's an example of my columns: Column A | Column B | Column C | 06-oct-04 12-oct-04 12-oct-04 07-oct-04 08-oct-04 08-oct-04 08-oct-04 03-oct-04 09-oct-04 04-oct-04 10-oct-04 05-oct-04 11-oct-04 06-oct-04 06-oct-04 12-oct-04 07-oct-04 07-oct-04 13-oct-04 08-oct-04 08-oct-04 I realize that if I delete rows 3, 4 & 5 (in this example) that I will lose the data in columns A & B. That's okay. All I really care about is column C. I hope this is clear and not confusing. Thanks for any help. Sharon |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Running two formulas as one | Excel Worksheet Functions | |||
Running all data through a set of formulas | Excel Discussion (Misc queries) | |||
How can I save the formulas when running a macro? Need ASAP!! | Excel Discussion (Misc queries) | |||
Running a macro if any data is entered in a range of cells | Excel Worksheet Functions | |||
How to skip empty cells while running a macro? | Excel Programming |