+ Reply to Thread
Results 1 to 11 of 11

Set range H5: H#### where ### is a number from a cell stored in HD4

Hybrid View

  1. 12-03-2014, 03:13 PM #1
    sdl2
    sdl2 is offline
    Registered User
    Join Date
    08-12-2014
    Location
    CA
    MS-Off Ver
    2011
    Posts
    73

    Set range H5: H#### where ### is a number from a cell stored in HD4

    Hi guys,
    All I want to do is set the range as H5:H#### where #### is a number stored in a cell. In other words the range is dynamic.

    Range("H5:H&"HD4"&").AutoFilter Field:=8, Criteria1:=Range("H2").Value
    Additionally, I'd like to filter multiple criteria starting with criteria1 and criteria 2 will filter from criteria 1 and so on... can I just use

    Range("H5:H&"HD4"&").AutoFilter Field:=8, Criteria1:=Range("H2").Value
Range("H5:H&"HD4"&").AutoFilter Field:=8, Criteria2:=Range("H3").Value
Range("H5:H&"HD4"&").AutoFilter Field:=8, Criteria3:=Range("H4").Value
Range("H5:H&"HD4"&").AutoFilter Field:=8, Criteria4:=Range("H5").Value
    Thanks,
    Sam
    Reply With QuoteRegister To Reply
  2. 12-03-2014, 03:34 PM #2
    hoyasaxa215
    hoyasaxa215 is offline
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: Set range H5: H#### where ### is a number from a cell stored in HD4

    You can accomplish this using the =INDIRECT function.

    https://support.office.com/en-us/art...b-d4007d89477a
    Reply With QuoteRegister To Reply
  3. 12-03-2014, 08:47 PM #3
    sdl2
    sdl2 is offline
    Registered User
    Join Date
    08-12-2014
    Location
    CA
    MS-Off Ver
    2011
    Posts
    73

    Re: Set range H5: H#### where ### is a number from a cell stored in HD4

    I'm afraid I don't know how to do this in VBA. Right now I have a bunch of rows that go to 14000 and increase daily and 1 row goes to 50,000. I only need to filter the list that goes to 14k as it changes not all the way to 50k. This takes too long.
    Reply With QuoteRegister To Reply
  4. 12-03-2014, 08:49 PM #4
    Debraj Roy
    Debraj Roy is offline
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Set range H5: H#### where ### is a number from a cell stored in HD4

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    Regards!
    =DEC2HEX(3563)

    If you like someone's answer, click the star to give them a reputation point for that answer...
    Reply With QuoteRegister To Reply
  5. 12-03-2014, 09:10 PM #5
    sdl2
    sdl2 is offline
    Registered User
    Join Date
    08-12-2014
    Location
    CA
    MS-Off Ver
    2011
    Posts
    73

    Re: Set range H5: H#### where ### is a number from a cell stored in HD4

    Please see the attached, by the way I need to search every single field so if there is anything I need to be wary of please advise

    Data_For_filter.xlsb
    Reply With QuoteRegister To Reply
  6. 12-03-2014, 09:16 PM #6
    Debraj Roy
    Debraj Roy is offline
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Set range H5: H#### where ### is a number from a cell stored in HD4

    OK.. thats the story..

    try to grasp it.. 

        Range("H4:H" & Range("Hd4").Value).AutoFilter Field:=1, Criteria1:=Range("H2").Value
    'or
    'Range("A4:H" & Range("Hd4").Value).AutoFilter Field:=8, Criteria1:=Range("H2").Value
    Reply With QuoteRegister To Reply
  7. 12-03-2014, 09:24 PM #7
    sdl2
    sdl2 is offline
    Registered User
    Join Date
    08-12-2014
    Location
    CA
    MS-Off Ver
    2011
    Posts
    73

    Re: Set range H5: H#### where ### is a number from a cell stored in HD4

    Quote Originally Posted by Debraj Roy View Post
    OK.. thats the story..

    try to grasp it.. 

        Range("H4:H" & Range("Hd4").Value).AutoFilter Field:=1, Criteria1:=Range("H2").Value
    'or
    'Range("A4:H" & Range("Hd4").Value).AutoFilter Field:=8, Criteria1:=Range("H2").Value
    Ug I knew it was so simple. I was so close. If I post the code I have now for the complete setup would you be able to offer any suggestions to make it faster? Your call of course.
    Reply With QuoteRegister To Reply
  8. 12-03-2014, 09:27 PM #8
    sdl2
    sdl2 is offline
    Registered User
    Join Date
    08-12-2014
    Location
    CA
    MS-Off Ver
    2011
    Posts
    73

    Re: Set range H5: H#### where ### is a number from a cell stored in HD4

    p1 of 2 
    Sub FilterDataChoices()
    Application.ScreenUpdating = False
    Sheets("Raw Data").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!RC&""*"""
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[3]C[-1]&""*"""
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[6]C[-2]&""*"""
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[9]C[-3]&""*"""
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[12]C[-4]&""*"""
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[15]C[-5]&""*"""
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[12]C[-4]&""*"""
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[6]C[-5]&""*"""
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[9]C[-6]&""*"""
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[6]C[-5]&""*"""
    Range("K2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[9]C[-6]&""*"""
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[18]C[-7]&""*"""
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[23]C[-12]&""*"""
    Range("N2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[26]C[-13]&""*"""
    Range("O2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[29]C[-14]&""*"""
    Range("P2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[32]C[-15]&""*"""
    Range("Q2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[6]C[-10]&""*"""
    Range("R2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[3]C[-10]&""*"""
    Range("S2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[9]C[-12]&""*"""
    Range("T2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[12]C[-13]&""*"""
    Range("U2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[15]C[-16]&""*"""
    Range("V2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[18]C[-21]&""*"""
    Range("W2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[15]C[-16]&""*"""
    Range("X2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[21]C[-17]&""*"""
    Range("Y2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[49]C[-24]&""*"""
    Range("Z2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[49]C[-21]&""*"""
    Range("AA2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[51]C[-26]&""*"""
    Range("AB2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[51]C[-26]&""*"""
    Range("AC2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[51]C[-24]&""*"""
    Range("AD2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[53]C[-29]&""*"""
    Range("AE2").Select
    ActiveCell.FormulaR1C1 = "=Design_Master!R[53]C[-29]&""*"""
    Range("AE2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[53]C[-29]&""*"""
    Range("AF2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[53]C[-27]&""*"""
    Range("AH2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[55]C[-33]&""*"""
    Range("AI2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[55]C[-33]&""*"""
    Range("AJ2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[55]C[-31]&""*"""
    Range("AK2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[57]C[-36]&""*"""
    Range("AL2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[57]C[-36]&""*"""
    Range("AM2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[57]C[-34]&""*"""
    Range("AN2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[44]C[-35]&""*"""
    Range("AO2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[44]C[-35]&""*"""
    Range("AP2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[35]C[-41]&""*"""
    Range("AQ2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[35]C[-38]&""*"""
    Range("AR2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[2]C[-26]&""*"""
    Range("AS2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[2]C[-29]&""*"""
    Range("AT2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!RC[-30]&""*"""
    Range("AU2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!RC[-29]&""*"""
    Range("AV2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!RC[-28]&""*"""
    Range("AW2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!RC[-27]&""*"""
    Range("AX2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[11]C[-34]&""*"""
    Range("BA2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[21]C[-30]&""*"""
    Range("BB2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[43]C[-38]&""*"""
    Range("BC2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[43]C[-37]&""*"""
    Range("BD2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[46]C[-38]&""*"""
    Range("BE2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[46]C[-37]&""*"""
    Range("BF2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[46]C[-38]&""*"""
    Range("BG2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[43]C[-37]&""*"""
    Range("BH2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[46]C[-38]&""*"""
    Range("BI2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[49]C[-40]&""*"""
    Range("BJ2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[43]C[-38]&""*"""
    Range("BK2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[49]C[-43]&""*"""
    Range("BL2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[52]C[-44]&""*"""
    Range("BM2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[46]C[-42]&""*"""
    Range("BN2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[46]C[-42]&""*"""
    Range("BO2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[54]C[-51]&""*"""
    Range("BP2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[57]C[-52]&""*"""
    Range("BQ2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[1]C[-44]&""*"""
    Range("BR2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[1]C[-43]&""*"""
    Range("BS2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[3]C[-46]&""*"""
    Range("BT2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[3]C[-45]&""*"""
    Range("BU2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[5]C[-48]&""*"""
    Range("BV2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[5]C[-47]&""*"""
    Range("BW2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[7]C[-50]&""*"""
    Range("BX2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[7]C[-49]&""*"""
    Range("CC2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[21]C[-56]&""*"""
    Range("BY2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[11]C[-52]&""*"""
    Range("BZ2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[13]C[-53]&""*"""
    Range("CA2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[15]C[-54]&""*"""
    Range("CB2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[17]C[-55]&""*"""
    Range("CD2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[23]C[-57]&""*"""
    Range("CE2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[7]C[-51]&""*"""
    Range("CF2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[2]C[-52]&""*"""
    Range("CG2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[5]C[-53]&""*"""
    Range("CH2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[2]C[-52]&""*"""
    Range("CI2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[7]C[-52]&""*"""
    Range("CJ2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[5]C[-53]&""*"""
    Range("CK2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[5]C[-56]&""*"""
    Range("CL2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[7]C[-56]&""*"""
    Range("CM2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[40]C[-66]&""*"""
    Range("CN2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[43]C[-67]&""*"""
    Range("CO2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[35]C[-68]&""*"""
    Range("CP2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[35]C[-66]&""*"""
    Range("CQ2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[35]C[-65]&""*"""
    Range("CR2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[35]C[-64]&""*"""
    Range("CS2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[35]C[-62]&""*"""
    Range("CT2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[37]C[-73]&""*"""
    Range("CU2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[37]C[-71]&""*"""
    Range("CV2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[37]C[-70]&""*"""
    Range("CW2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[35]C[-69]&""*"""
    Range("CR2").Select
    Sheets("Design_Master").Select
    Rows("37:37").Select
    Range("O37").Activate
    Sheets("Raw Data").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[37]C[-64]&""*"""
    Range("CX2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[35]C[-68]&""*"""
    Range("CY2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[35]C[-68]&""*"""
    Range("CZ2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[55]C[-70]&""*"""
    Range("DA2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[37]C[-104]&""*"""
    Range("DB2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[18]C[-99]&""*"""
    Range("DC2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[37]C[-102]&""*"""
    Range("DD2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[25]C[-83]&""*"""
    Range("DE2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[49]C[-91]&""*"""
    Range("DF2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[49]C[-108]&""*"""
    Range("DG2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[47]C[-109]&""*"""
    Range("DH2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[37]C[-78]&""*"""
    Range("DI2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[21]C[-108]&""*"""
    Range("DJ2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[47]C[-109]&""*"""
    Range("DK2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[47]C[-114]&""*"""
    Range("DL2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[52]C[-100]&""*"""
    Range("DM2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!RC[-112]&""*"""
    Range("DN2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[9]C[-84]&""*"""
    Range("DO2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[49]C[-94]&""*"""
    Range("DP2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[52]C[-95]&""*"""
    Range("DQ2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[55]C[-96]&""*"""
    Range("DS2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[49]C[-100]&""*"""
    Range("DT2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[49]C[-100]&""*"""
    Range("DU2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[58]C[-100]&""*"""
    Range("DV2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[46]C[-101]&""*"""
    Range("DX2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[49]C[-121]&""*"""
    Range("DY2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[51]C[-122]&""*"""
    Range("DZ2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[53]C[-123]&""*"""
    Range("EA2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[55]C[-124]&""*"""
    Range("EB2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[57]C[-125]&""*"""
    Range("EC2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[49]C[-117]&""*"""
    Range("EE2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[2]C[-100]&""*"""
    Range("EO2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[33]C[-111]&""*"""
    Range("EP2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[29]C[-121]&""*"""
    Range("ES2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[39]C[-129]&""*"""
    'Range("ET2").Select
    'ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[35]C[-149]&""*"""
    Range("EU2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[39]C[-150]&""*"""
    Range("EV2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[39]C[-147]&""*"""
    Range("EW2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[35]C[-151]&""*"""
    Range("EX2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[37]C[-152]&""*"""
    Range("EY2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[39]C[-153]&""*"""
    Range("EZ2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[33]C[-121]&""*"""
    Reply With QuoteRegister To Reply
  9. 12-03-2014, 09:35 PM #9
    Debraj Roy
    Debraj Roy is offline
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Set range H5: H#### where ### is a number from a cell stored in HD4

    Quote Originally Posted by sdl2 View Post
    If I post the code I have now for the complete setup would you be able to offer any suggestions to make it faster?
    only If you post the Sheet.. and let us know the FLOWCHART.. or requirement.. in plain language..
    Reply With QuoteRegister To Reply
  10. 12-04-2014, 04:01 PM #10
    sdl2
    sdl2 is offline
    Registered User
    Join Date
    08-12-2014
    Location
    CA
    MS-Off Ver
    2011
    Posts
    73

    Re: Set range H5: H#### where ### is a number from a cell stored in HD4

    Quote Originally Posted by Debraj Roy View Post
    only If you post the Sheet.. and let us know the FLOWCHART.. or requirement.. in plain language..
    Hi Debraj,
    I have been having great trouble with this. What I have in the Design Form is an unchangable layout for our product. The "flow" is that a user enters any data in the design master, that may be a partial match or more than one criteria. The data types are numerical, text and date. Then when the user clicks search records, records that match are returned. Basically filter the raw data. I have only shown the first few hundred records. There are 14k and growing. Let me know what you think!

    Module 5 contains the code that pertains to the search record button and what I am trying to do.

    Thanks for the help!!!
    DesignMasterCodeCleanup.xlsb
    Reply With QuoteRegister To Reply
  11. 12-03-2014, 09:28 PM #11
    sdl2
    sdl2 is offline
    Registered User
    Join Date
    08-12-2014
    Location
    CA
    MS-Off Ver
    2011
    Posts
    73

    Re: Set range H5: H#### where ### is a number from a cell stored in HD4

    p2 of 2
     Range("FA2").Select
    Sheets("Design_Master").Select
    Sheets("Raw Data").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[9]C[-132]&""*"""
    Range("FB2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[9]C[-131]&""*"""
    Range("FM1").Select
    Sheets("Raw Data").Select
    Range("FM2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[19]C[-144]&""*"""
    Range("FN2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[47]C[-163]&""*"""
    Range("FS2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[46]C[-159]&""*"""
    Range("FT2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[35]C[-169]&""*"""
    Range("GE1").Select
    Sheets("Design_Master").Select
    Range("P37").Select
    Sheets("Raw Data").Select
    Range("GU2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[19]C[-185]&""*"""
    Range("GV2").Select
    ActiveCell.FormulaR1C1 = "=""*""&Design_Master!R[21]C[-186]&""*"""
    Range("HB2").Select
     Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=IF(OR(R[-1]C=""**"",R[-1]C=""*0*"")=TRUE,"""",R[-1]C)"
    Range("A3").Select
    Selection.AutoFill Destination:=Range("A3:HC3"), Type:=xlFillDefault
    Range("A3:HC3").Select
    Range("A3").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(OR(R[-1]C=""**"",R[-1]C=""*0*"",R[-1]C="""",R[-1]C=""*N/A*"",R[-1]C=""*-0.002*"",R[-1]C=""*0.003*"")=TRUE,"""",R[-1]C)"
    Range("A3").Select
    Selection.AutoFill Destination:=Range("A3:HC3"), Type:=xlFillDefault
    Range("A3:HC3").Select
    Range("HB2").Select
    Selection.ClearContents
    Range("HB3").Select
    Selection.ClearContents
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Rows("3:3").Select
    Selection.Copy
    Rows("2:2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Rows("3:3").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Range("A4:HC100000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange _
    '    :=Range("A1:HC2"), Unique:=False
    'Selection.AutoFilter Field:=8, Criteria1:=Range("H2").Value
    Application.ScreenUpdating = True
    'MsgBox ((Cells(5, 8)) & (Cells(1, 212)))
    'Range(Cells(5, 8), Cells(1, 212)).AutoFilter Field:=8, Criteria1:=Range("H2").Value
    Range("H4:H" & Range("HD4").Value).AutoFilter Field:=8, Criteria1:=Range("H2").Value
    'Range(Cells(5, 8), Cells(1, 212)).AutoFilter Field:=42, Criteria1:=Range("AP2").Value
End Sub
'Range(Cells(1,1), Cells(10,5))   '=Range("A1:E10")
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Converting a range of numbers stored as text to number format w/o looping
    By Excel_vba in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-23-2013, 04:40 AM
  2. How to select a cell based on a row number I have stored
    By ChrisF79 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-07-2013, 02:11 PM
  3. [SOLVED] Compare what cell is stored in a Range variable.
    By GaidenFocus in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-12-2012, 03:05 PM
  4. [SOLVED] HOW TO FIND THE COLUMN OR ROW NUMBER of a address stored in a cell
    By chrismonica in forum Excel General
    Replies: 3
    Last Post: 07-07-2012, 10:43 AM
  5. [SOLVED] Equation Referencing Row Number Stored in Cell
    By Jim Hagan in forum Excel General
    Replies: 1
    Last Post: 04-13-2005, 04:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1