CODEWITHSUNDEEP
excelvba
user3739106
user3739106

Reputation: 13

VBA regarding Data in a Range

I am new to VBA and I am stuck on this problem. I have a big data for temperature:

    A
1.  21.40
2.  21.45
3.  21.38
4.  22.89
5.  23.27
etc...

I put the data in column A and for each temperature I want to assign their density and heat coefficient values in column B (respective to the temperature). I tried this:

Dim Temp As Range, Density As Double

Set Temp = Range(“A1:A1000″)    
Temp = Range(“A1:A1000″).Value

If Temp = 21 Then    
    Density = 998.08    
ElseIf Temp=22 Then    
    Density = 997.86    
ElseIf…(I do until the required Temp)

End If

Range(“B1:B1000″).Value = Density

I get a mismatch error. Could you please help me?

Upvotes: 0

Views: 287

Answers (1)

Netloh
Netloh

Reputation: 4378

There are numerous ways to do this, but I would suggest you to change your code into something like this:

Sub Temperature()
    Dim Temp As Range
    Dim Density As Double

    Set Temp = ActiveSheet.Range("A1:A1000")

    For Each cell In Temp
        Select Case cell.Value
            Case 20 To 21: Density = 998.08
            Case 21 To 22: Density = 997.86
            '...and so on with the cases
            Case Else: Density = 0
        End Select
        cell.Offset(0, 1).Value = Density '<~~ Writes the density in column B
    Next cell
End Sub

This looks at each value in each cell of range A1:A1000 and then determines what the density for the associated value should be (based on the inputs in the code). It hereafter writes this value to the cell with the same row number in column B and then continues on to the next cell.

I am no expert on temperatures and density, but it looks to me like you would need to assign a density based on a temperature interval. That is why I have based the Select Case on temperature intervals.

Upvotes: 2

Related Questions