Posts by Billyrj

    I have to admit i may have made this complicated but i honestly do not see my error.

    I have a Cell Validation that selects an name on sheet TEST1 "F6". Works FIne i get the list of names from sheet TEST2 column "F".

    The formula in Cell validation "F6" is =OFFSET(test2!$F$2,0,0,COUNTA(test2!$F$1:$AF$10)).

    My desire is to get The numerical value using =VLOOKUP(F6,test2!A2:F10,1,0) looking in test2.

    I have and 2 examples and the AS IS works by Cell Validation and looking for the numerical value and adding name but i now have a need to work project in opposite fashion.


    Always appreciative for help

    Billyrj

    Files

    • Test.xlsx

      (389.31 kB, downloaded 63 times, last: )

    royUK i figured it out. I had to modify my text in my datevalue formula and now i just reference a cell that i use for this report. Thanks again


    Code
    1. =(DATEVALUE("9/1/2022")-I2)/365
    2. Changed to
    3. =($V$1-I2)/365

    In this case its a time between two dates. Every year i have to change "9/1/2022" to the following year so i was desiring to ref a cell that is is changed so i do not have to do so on multiple pages.

    Example:


    Code
    1. =(DATEVALUE("9/1/2022")-I2)/365
    2. written out looks like ***This is with I2 written out***
    3. =(DATEVALUE("9/1/2022")-(2/1/1996)/365 which equals 26.2

    I am working a project that every year i have to change a formula and would desire automate. I beleive i am using date value as intended but desire to ref a cell so i can change on 40+ sheets all at once and not separately. I would ref U2


    Code
    1. =(DATEVALUE("9/1/2022")-I2)/365

    I did and seems it should work but not sure why. Sorry i did not copy correct code. The only issue is that it does not exclude Run Zones work sheet and it is correctly spelled.


    Code
    1. If oWs.Name <> "Run Zones" Then oWs.Range("P2:P10000").Interior.Color = xlNone

    royuk thanks i ran the second code and got an error compile error and it highlights ".Count" portion of the code with the following message


    Compile error:

    for each may only iterate over a collection object or an array

    Code
    1. Sub ClearFill()
    2. Dim oWs As Worksheet
    3. For Each oWs In ThisWorkbook.Worksheets.Count
    4. If oWs.Name <> "enter sheet name here" Then oWs.Range("P2:P10000").Interior.Color = xlNone
    5. Next oWs
    6. End Sub

    I have a rather large workbook about 25 sheets. I was attempting to write a code that clears fill color P2:P10000 on all sheets minus sheet1. I have recorded macro and i can do each individual sheet but was attempting to simplify.


    1: Sheet 1 is named dashboard and do not need to do anything on this sheet all other sheets i need to clear fill color in columns P2:P10000 (end of sheet) on all subsequent sheets


    thanks

    Mumps it does as i thought. Once i transitioned my example to my workbook I had to shift some columns and it works but i get a run-time error Type Mismatch highlighted below. Any ideas. The problem arose when i moved from "F3" to "R3". I did get the bug but it coded perfectly. Thanks


    I think i misunderstand...If i changed from "F3" to "R3" do I need to change the Resize Value? I thought it was pretty straight forward but i didnt understand as well as I thought. The Code works as written but needed to tighen a sheet so just trying to understand the resize portion of the code



    Code
    1. vDate1 = srcWS.Range("F3", srcWS.Range("F" & srcWS.Rows.Count).End(xlUp)).Resize(, 4).Value
    2. change to
    3. vDate1 = srcWS.Range("R3", srcWS.Range("R" & srcWS.Rows.Count).End(xlUp)).Resize(, 4).Value

    Column "B" reflects year+month and references "C1" to dictate what month to begin with and in this case 10 (OCT).

    Good example: A6 has a calendar Year date of 20221001 and its Fiscal Year is 2023 because the FY Starts OCT for budgeting reasons.


    Quote

    The macro loops through the years in column F of the dashboard and finds all the cells in column A of each sheet that contain each year and then colors the corresponding cells in column A with the color from column I in the dashboard.

    I was thinking if we loop through column "F" of the dashboard and find in Column "B" and color "A" with the color from the dashboard. Its convoluted but i was unable to figure out how to do any other way with existing dates. Thanks

    I am attempting to color code the Fiscal Years and for this project it start in October. The current macro correctly annotates the year and not FY. This project years go OCT to OCT. I created column "B" as i thought it would be easier to look in "B" and fill color accordingly in "A". Apologies if i did not properly clarify.