Macro referencing Column

[Saisaisai]

Hi guys,

I'm trying to reference the values from column F and put it into column Y. This is the code I have:


Range("Y22:Y" & Cells(Rows.Count, "F").End(xlUp).Row).Formula = "=F22"


My problem is that this formula can handle columns that end at various rows but it isn't dynamic for the start of the row. The start is hard coded at Y22 which links to F22 and the row above it (F21) is text which I want to ignore.


help me make this more dynamic so it doesn't matter where the first and last data points are in a column

thanks

[SamT]

How do we tell which Row is supposed to be the start Row?

[p45cal]

try:

lr = Cells(Rows.Count, "F").End(xlUp).Row
Set myrng = Range("F1:F" & lr).SpecialCells(xlCellTypeConstants, 2)
For Each cll In myrng.Cells
  fr = cll.Row
Next cll
fr = fr + 1
If lr >= fr Then Range("Y" & fr & ":Y" & lr).Formula = "=F" & fr

[Saisaisai]

Thank! appreciate the help. Just one follow up, I tried manipulating the formula for another task. I want column X to be filled with the formula Column C - Column N.

lr = Cells(Rows.Count, "C").End(xlUp).Row
Set myrng = Range("C1:C" & lr).SpecialCells(xlCellTypeConstants, 2)
For Each cll In myrng.Cells
    fr = cll.Row
Next cll
fr = fr + 1
If lr >= fr Then Range("X" & fr & ":X" & lr).Formula = "=C-N" & fr

unfortunately it didn't work

strangely though when I put in replaced the last lines formula from "=C-N" to "C22-N" it worked...In the code it doesn't seem like those are even in the same row but when I run the macro it populates the column with the correct cells



and lastly in column X after the output is finished what would the next code be so that the cell right above the first populated cell in column x has the word "entered" in it?

super appreciate it

[p45cal]

If lr >= fr Then Range("X" & fr & ":X" & lr).Formula = "=C" & fr & "-N" & fr
Cells(fr - 1, "X").Value = "entered"

[Saisaisai]

OHHH THANK YOU SOOO MUCH I LOVE YOUUUU!!!

[p45cal]

I don't think that was correct. I plugged in the formula and it populated all of coulmn x with "=X:X-N"

That will only happen if fr and lr are both Empty.
That might happen if you added On Error resume next and the column in question was completely blank.

[Saisaisai]

That will only happen if fr and lr are both Empty.
That might happen if you added On Error resume next and the column in question was completely blank.

Sorry to bug you on more question.


So in column A the first entry in the table is Queue Name with a list of all the names below it. How can I code to highlight the entire row for every cell that contains the word Apple or Orange. So for example if cell in column A has Candy_Apple, Apple_seg1, Apple_master, Orange, Orange_3232 ect. the entire row is highlighted red.


Last thing apologies,


If a row is highlighted, I want to go to column X and sum all the highlighted numbers.
Then go down 2 cells from the last row of column X and put the result there.


I'm sorry for bugging you. I'm learning this as I go along and I have a work project due. Thank you, you have no idea how much I appreciate this.

[mdmackillop]

Please post a workbook with sample data.

[Saisaisai]

So basically I would like to know how to have the rows that contain either the word apple or orange in their queue name to be highlighted red and rows that contain Make, Lex, and Tanner to be highlighted blue. Then in column E and F, I would like to sum in group 1 all the reds and in group 2 all the blues (this table has to be 2 rows below table).


Thank you. I've been hitting my head.

[p45cal]

In the attached:

Conditional formatting applied to A9:P18
Dependent on values in R7:R8 and in T7:T9

Independent of this formatting (but not the values in R7:R8 and in T7:T9):

• Formulae in I9:I18, with header Grp added,
• then 2 solutions offered:
  
  1. Formulae in E28:G239
  2. Pivot table in D24:G26 (needing dummy headers added to cells D8 and H8) with a calculated field (Field1) for the rightmost values.

[Saisaisai]

Apologies, I should've been clearer. Is there anyway to out put that as VBA code? because that sample is the exact format I have to always report in for our generated reports. The amount of rows may vary but everything else remains the same. If not, thank you for your help much appreciated

side note: just curious. Set myrng = Range("F1:F" & lr).SpecialCells(xlCellTypeConstants, 2)
What does the Range argument say? is it saying everything in coulumn F until the last row? I don't understand the "&" operator

[SamT]

Range Addresses are Strings, ie, "A1"
The "&" Operator converts the "lr" number to a String and Concatenates it with the rest of the Range Address.

is it saying everything in coulumn F until the last row?

yes