# Posts by Sicarii

• ## Check For Empty Cells In Non Contiguous Range

Re: Check Range Isempty

Not sure on exact reasoning behind it all, but did find more that could be useful to you...

http://www.ozgrid.com/VBA/VBALoops.htm

• ## Count Consecutive Numbers

Re: Consecutive Numbers

There are sooooo many similiar posts, but...

I believe I used krishnakumar's...

=IF(MAX(FREQUENCY(IF(A1:I1=0,ROW(A1:I1)),IF(A1:I1<>0,ROW(A1:I1))))>=3,0,SUM(A1:I1))

• ## Disable Solver Message Asking To Replace Cell Contents

Re: Solver Message - Not Able To Disable It

Have you tried:

Code
``SolverSolve True``
• ## Copy Data From Another Sheet If Adjacent Cell Meet Criteria

Re: Copy Data From Another Sheet If Adjacent Cell Meet Criteria

If there is only a single cell that has the value you're looking for and since it's to the left of your target, have you looked at VLOOKUP?

• ## Check For Empty Cells In Non Contiguous Range

Re: Check Range Isempty

When typing the code, the tooltip shows the cells in the range need to be seperated by commas...

Code
``````If IsEmpty(Range("A1,B1,C1,D1,E1,F1")) Then
Range("G8") = "All mT"
End If``````
• ## Check For Letter In Range & Count Numbers

Re: Compare A Cell To A Range Of Cells Based On Criteria

The spreadsheet design makes it difficult to just have one formula to pull down...so you'd have to adjust your range to match each trip range.

E6: =IF(COUNTIF(C\$6:C\$9,"E")=COUNTA(B\$6:B\$9),"Mixed Use","")

E10: =IF(COUNTIF(C\$10:C\$19,"E")=COUNTA(B\$10:B\$19),"Mixed Usage","")

• ## VBA To Color Range Based On Cell Value

Re: Case Else Not Including Errors

Assuming you have a formula in the sheet that is resulting in the 'N/A' error...

Look at ISNA to just eliminate the problem at the source...

• ## Best Practice Worksheet Struture

Re: Restructure Data

You're not really providing much to go on...how about a sample of the file?

• ## Elapsed Hours From Date & Time

Re: Date Time Elapsed Hours

=(A2-A1)*24

...yields 16.75 (Format as General)

• ## Prevent N/A In Lookup Formulas

Re: Sum Only Good Data

Or a better approach would eb to eliminate the #N/A's...place this in I9 to see what I mean:

=IF(ISNA(VLOOKUP(\$A9,'11583'!\$G\$24:\$J\$43,3,FALSE)),0,VLOOKUP(\$A9,'11583'!\$G\$24:\$J\$43,3,FALSE))

It's saying that if that vlookup returns an #N/A to place a 0, otherwise do the vlookup...

More Efficient Ways to stop #N/A Errors
Stop The #N/A! Error in VLOOKUP and other Lookup Functions

• ## Count & Compare Used Rows On 2 Sheets

Re: Counting Rows

What type of data is in the columns you're looking at? If it truly is all text...

=IF(COUNTA(A3:A5)<>COUNTA(Sheet2!B3:B5),"Error","Match")

• ## Extract Values That Exist In All Columns

Re: Extract Values That Exist In All Columns

A couple different options attached...

Personally I would put users down column A and list out the applications across the rest of the columns. You can quickly see a Yes/No status if everyone has the application. You could also generate a list of everyone that has all applications.

• ## Open Hyperlink With Keyboard Keys

I can't replicate that RTE unless my activecell does not include the hyperlink...try moving off that cell (if it was already the activecell when you did the code) and back on, then re-run...

• ## Open Hyperlink With Keyboard Keys

If you're already moving the mouse to "arrow over" the Hyperlink, why not just push the mouse button?

• ## Prevent Certain Characters In TextBox

Re: Check If Certain Characters Are Used

Using the Exit event, maybe use the InStr function to check if any of those values exist...

• ## Fix Formula Cell Reference

Re: Fill Series With Formula That Doesnt Change

Use an absolute reference with the \$ sign, i.e.

=A1*Sheetb!C\$1

...would keep it to cell C1, and if you wanted to pull horizontally and keep your column...

=A1*Sheetb!\$C1

...and you can use both

=A1*Sheetb!\$C\$1

• ## Change Value Based On Opposite Cell

Re: Change Value In Another Column

=if(a1="sha","shanghai",if(a1="sgn","ho Chi Minh",a1))

• ## Extract Cell Values By Conditions From Multiple Worksheets

Re: Extract Specific Cells In Multiple Sheets Into One Summary Sheet After Meeting A Cond

sounds like an IF statement would get your desired results...

• ## Search List For Matches From Another List

Re: Search Giant List For Matches From Another List

Vlookup?