range reference using defined term

[abitslow]

Hi there,
I have got my module to sort column C and then count the rows in column C which have a value under a certain criteria (the result defines the value of "x". In the next column I want to paste a certain value into the range from D2x . Any way of doing this?

Many thanks in advance.
Matt.

 

[SkipVought]

Matt,

Yes.




I gave you about as much information as you gave us. A bit more specific information on your part, might yield a better result for you, don't you think?

Skip,
[sub]
[red][/red]
[/sub]

 

[abitslow]

Sorry.
I have a spreadsheet in which people will paste three columns of data.
I'm writing a macro to sort this data by the third column C (Depth - which I've managed). It then has to work out how many of the entries in column C come above a depth figgure entered on a seperate sheet (Main).
From ther I need to paste in a third figure, entered on the other sheet (Main), into those rows. I have got an automatic count of the number of rows and can use this in a for - next loop, but don't know how to use this as part of a range.
Also, I need to sum a column (E) which I have atomatically entered in some other figures (using a loop to enter a formula result) and the use of the range would help here too.
I hope this is clearer. (!?)
Thanks.
m

 

[SkipVought]

You explain NOTHING about the actual logic.

What code do you have so far? What is not working?

Skip,
[sub]
[red][/red]
[/sub]

 

[Bong]

I think I can see what you're trying to do. I'm no expert but maybe I can help. Let's say that your depth tolerance value is in cell A1 on "Main", and the value you want to append to the table on the "shallow" rows is in cell A2 on "Main". Although you don't say, let's say your data sheet is called "Data". If the rows on Data are contiguous, you can get the entire table with usedrange, if not, you'll have to use something like Range("a65536").End(xlUp). Either way, you get a value for the last row, say, iLastRow, and let's just say the first row is 1.

lngDpthTol=sheets("Main").cells(1,1).value
strAppndVal=sheets("Main").cells(2,1).value
for i=1 to iLastRow
  if sheets("Data").cells(i,3).value<lngDpthTol then
     sheets("Data").cells(i,4)=strAppndVal
  end if
next

and it won't matter if the data are sorted before or after this.

_________________
Bob Rashkin

 

[abitslow]

Bong - thanks, that is neater that anything I had.

Skip, here is the code:

'Count Cells above CC
'method:  set i as the value entered for the closing contour
i = Worksheets("Main").Range("D33")

'method: set activecell for starting loop as top of depth in MLGrid
a = 2
Sheets("ML_Grid").Select
Cells(a, 3).Activate
ccCounter = 0

'method: loop through depth cells top down till value of closing contour is reached
'counter is increased to give value of "number of cells to CC".
Do
ccCounter = ccCounter + 1
a = a + 1
Cells(a, b).Activate

Loop Until ActiveCell > i


'ML_Grid GRV
a = 2
b = 4
c = 5
d = 6
grvmtyr = Sheets("Main").Range("K10")
p = ccCounter
GRV = 0

For q = 1 To p
Cells(a, d) = Cells(a, b) * Cells(a, c) * grvmtyr
GRV = GRV + Cells(a, d)
a = a + 1

Next q
Sheets("Main").Range("L39") = GRV

As you can see, I've made a work around so that it counts GRV during the loop rather than summing the range at the end - which I can't work out how to do.

I hope this is what you meant.
thanks again.