# how to find the maximum number

• ### Question

• Hello,

I have written for loop for finding the  maximum number in column.

I think it is possible to fing using object So i dont no how to use.

my code is

Dim high As Double
Dim j As Integer

high = 0
For j = 2 To 80
If high < Range("et" & j) Then
high = Range("et" & j)

End If
Next j

MsgBox (high)

and I a want to use variable instead of "et".

Thursday, September 13, 2012 9:08 AM

• offsett you can use only with range/cells not to column like:

``` Range("eu" & k).offset(,1) = "something"
'or
' cells(k,"eu").offset(,1) = "something else"```

regards

BDW

if you want to add some to COLs (I do not see that in declaration) or to max_kol, you can use (if they'r no.)

COLs = COLs + 1

Oskar Shon, Office System MVP

Thursday, September 13, 2012 10:15 AM

### All replies

• Can you make your question more clear. What is "et" and where is the column.

Regards,

ManjunathRV

Thursday, September 13, 2012 9:11 AM
• You do not have to using loop.

```Sub lklk()
Dim max_nr&
max_nr = Application.WorksheetFunction.Max(Range("J2:J80"))
MsgBox max_nr
End Sub```

Oskar Shon, Office System MVP

Thursday, September 13, 2012 9:20 AM
• Hello,

"et" is column name.

Thursday, September 13, 2012 9:21 AM
• if "et" is column Name then

```dim MyValdim MyColMyCol = Rows("1:1").Find(What:="et").ColumnMaxVal = Application.Max(Columns(MyCol))
```

Regards,

ManjunathRV

Thursday, September 13, 2012 9:28 AM
• BDW sorry ManjunathRV

that\your procedure not suppose work. You should use right statements in declaration and wonder how in row 1 find a letter of column

Oskar Shon, Office System MVP

Thursday, September 13, 2012 10:00 AM
• Thank you .

and I am trying to make some calculation with founded max number and it is  working correctly .

I just want to use variable ,so finding error .

my code is

Dim max_nr As Double
Dim scale_target As Double
scale_target = Me.TextBox2.Value
Dim k As Integer
Dim int_old As Double

max_nr = Application.WorksheetFunction.Max(Columns(COLs))
MsgBox (max_nr)
For k = 2 To 80
Range("eu" & k) = (scale_target / max_nr) * Range("et" & k)

Next k

here COLs is variable which holds the name of column . so " (scale_target / max_nr) * Range("et" & k)" this caluculation values i want to insert into next column of "COLs". therfore i am usin "eu" & k and "et"&k is the column of COLs .

can you help me how to use variable in this problem.

and I was trying of use column(cols).offset(,1).

but it is not working correctly

• Edited by Thursday, September 13, 2012 10:06 AM
Thursday, September 13, 2012 10:04 AM
• offsett you can use only with range/cells not to column like:

``` Range("eu" & k).offset(,1) = "something"
'or
' cells(k,"eu").offset(,1) = "something else"```

regards

BDW

if you want to add some to COLs (I do not see that in declaration) or to max_kol, you can use (if they'r no.)

COLs = COLs + 1

Oskar Shon, Office System MVP