Select Certain Cells within Range

**tejay** · 11-06-2015, 06:22 AM

Hi All,

Prior to getting to this stage;

a number of cells have already been selected.
Sheets(MySheetName).Range("A3") adds up those cells that = "#N/A Requesting Data..."

"c" in the code below is referring to these pre-selected cells.

What I was hoping to do was;

if the cell value is not "#N/A Requesting Data..." de-select the cell
for remaining cells that are selected, calculate those cells
continue to loop until Sheets(MySheetName).Range("A3") is 0.

Currently the code calculates all cells;

Sub Verify_Update()

MySheetName = Sheets("Main").Range("C37").Value & "_Data"

Sheets(MySheetName).Range("A3").Calculate

If Sheets(MySheetName).Range("A3").Value <> 0 Then

Dim c As Range
    
  For Each c In Selection.Cells
   If c.Value = "#N/A Requesting Data..." Then
            Sheets(MySheetName).Calculate
            Call Application.OnTime(Now + TimeValue("00:00:05"), "Verify_Update")
            Exit Sub
        End If
    Next c

Else: Sheets("List").Range("H1").Value = Sheets("List").Range("H1").Value + 1
Call Copy_Data

End If

End Sub

Any help would be great!

Thanks

**Richard Buttrey** · 11-06-2015, 06:34 AM

Hi,

Are there some cells that are NOT selected but have the value "#N/A Requesting Data..."

Incidentally would you add your location to your profile. It often helps when responding to questions.

**tejay** · 11-06-2015, 07:03 AM

Hi Richard,

Just confirming that all cells that have the value "#N/A Requesting Data..." are to be selected.
And the VBA code just keeps looping until a value is finally downloaded and there are no "#N/A Requesting Data..." cells left.

Thanks

Tejay

**Richard Buttrey** · 11-06-2015, 07:31 AM

Hi,

In that case can't you use a simple SUMIF or SUMIFS to sum the relevant column where the criteria column has the #N/A...text?

**kvsrinivasamurthy** · 11-06-2015, 07:37 AM

Try this

Dim celrng As Range

Set celrng =Sheets(MySheetName).cells.SpecialCells(xlCellTypeConstants)

  For Each c In celrng

"your code"

**kvsrinivasamurthy** · 11-06-2015, 07:33 AM

Duplicate deleted.

**tejay** · 11-06-2015, 06:59 PM

Hi All,

Thanks Kvsrinivasamurthy, I presume your code will just select any cell on "MySheetName" that has the value "#N/A Requesting Data..."?

Richard at the moment I think I am effectively doing that, I have a cell Sheets(MySheetName).Range("A3") that has:

IFERROR(IF(B6="#N/A Requesting Data...",1,0),0)+IFERROR(IF(D6="#N/A Requesting Data...",1,0),0)....

If Kvsrinivasamurthy code will just select any cell with "#N/A Requesting Data..." thats even better and will help with some other things.

In my original code I have;

   If c.Value = "#N/A Requesting Data..." Then
            Sheets(MySheetName).Calculate

Is it possible to just re-calculate the selected cells each time instead of the whole sheet?

Thanks

**Richard Buttrey** · 11-06-2015, 07:23 PM

Hi,

I'm struggling to understand why you need to select any cells if all you want is to sum all cells in a column that has "#N/A Requesting Data" in another column on the same row which can be done with a SUMIFS()

If we could see your workbook in which you have manually entered some typical results, telling us which are the result cells this would be easier to confirm.

**tejay** · 11-06-2015, 07:35 PM

Hi Richard,

All of the "#N/A Requesting Data" are not on the same row or column.
Here are two screen shots from the sheet;

delete1.JPG

delete2.JPG

So I know where the "#N/A Requesting Data" will appear.

The code I had was to keep looping until the sum of all those cells summed to 0.
They can sum to 0 by either spitting out data or an error, in both cases the "#N/A Requesting Data" disappears.

So what would be great is if;

Only the cells which contain "#N/A Requesting Data" are selected
Only the selected cells are re-calculated

Hope that makes more sense?

Thanks

**Richard Buttrey** · 11-06-2015, 07:38 PM

Hi,

Upload the workbook as suggested with some manually entered results - make sure you cover all combinations/permutations and maybe we can make some progress.

**tejay** · 11-06-2015, 07:49 PM

Hi Again,

For this to work, it needs to be connected to a data terminal which I only have at work.

Eitherway, attached is a simplified version of this spreadsheet.

Cell A3 sums all the cells that data is to appear in.
And the VBA code is to keep looping to force data to download until either it does or the data terminal displays an error value.

Thanks for your help

Tejay