I am a VB noob and am trying to do the following. I am trying to copy the contents of a cell that I am changing. I have a cell with DBT that I am varying from 32-100. What I am trying is, each time the cell changes from say 32 to 33 I want to record the value in a cell. So I want a column that goes

32
33
34
.
.
and so on. So far I am only getting a repeat of the last number i.e.

100
100
100
...

and so on.

This is what I have so far

Sub Lookuptable()

For x = 31 To 100

Cells(9, 2).Select 'This is my cell with the DBT that I am varying'
Selection.Value = x + 1

'Cells(10, 2).Select' ' This is for future use to create something similar horzontally'


For y = 1 To 70
Cells(44 + y, 2).Select 'Trying to write my data in this cell'
Selection.Value = "=+B9" ' This is my error I just want to pull the value'

Next y



Next x


End Sub

3/1/2011 10:07:53 PM

Your inner loop is overwriting that column with 70 copies of the same number.

Also I expected a thread about this: https://code.google.com/p/vba-rerecording/


[Edited on March 1, 2011 at 10:12 PM. Reason : or http://vba-m.com/

3/1/2011 10:10:24 PM

How do I fix it ?

3/1/2011 10:15:54 PM

Why not this?


x=31
j=1
While x<=100
Range("B"&j) = x
j=j+1
x=x+1
Wend


What do you mean by DBT?

3/1/2011 10:23:57 PM

Dry bulb temperature

3/1/2011 10:24:57 PM

ok. No idea why you want to assign the value to a cell, and then copy it...but...

This:

Selection.Value = "=+B9" ' This is my error I just want to pull the value'

Should be this:

Selection.Value = Range("B9").Value

3/1/2011 10:28:38 PM

^ Tried it same problem... I am trying to create a lookup table that I plan to use later.. right now I just have a psych chart spreadsheet that lets me put in values for dry bulb and wet bulb temperature and look at the corresponding values of enthalpy.. I want to create a table using this spreadsheet (dry bulb on one axis, wet bulb on the other and and the corresponding enthalpy values inside) which I can reference later..

[Edited on March 1, 2011 at 10:32 PM. Reason : ..]

3/1/2011 10:30:01 PM

Are you typing each entry in manually? If so there's no reason to bother with vba...just type into the cells themselves. Vba will save no time.

3/1/2011 10:35:29 PM

I am not typing the entries manually.. I have setup a counter in my code that runs that cell from 32 to 100.. I just need the values copied everytime that cell changes.. Once that happens I have to figure out how to copy the corresponding enthalpies (which change with respect to the temperature)..

3/1/2011 10:38:16 PM

Don't use the .Select method. It returns a Range object which is a pain in the ass to deal with in my opinion.

If you're just operating on a single cell at a time use Cells(row,column).Value, or, I think you can even drop the .Value part and you'll get the contents of the Cell in a Variant type of repreentation (ie, if its numbers you'll be able to add, if its text you can use the string operations).

3/2/2011 6:12:53 AM

I have anotehr question.. This time it is a different problem. I am trying to average the rows of a column. But I want to average the first 11 rows store it in a cell then acerage teh next 11 rows store it in another cell and so on.. for example

A
1
2
3
4
5
6
7
8
9
10
11
12
13
14...

I want to average rows 1 through 11 and store in B1. then i want to average 12 through 23 and store in B2 and so on.. anyone know how to do this?

4/8/2011 4:52:56 PM

currentRow = 1
currentSum = 0
rowCount = 0
avgCount = 1

While Cells(currentRow,1) > 0

   currentSum = currentSum + Cells(currentRow,1)
   rowCount = rowCount + 1
   if rowCount = 11 Then
      Cells(avgCount,2) = currentSum/11
      avgCount = avgCount + 1
      rowCount = 0
      currentSum = 0
   End if
  currentRow = currentRow + 1

Wend

4/8/2011 11:17:07 PM

Thanks for helping out a vba noob

4/9/2011 10:12:06 AM