PDA

View Full Version : Solved: macro doesn't work



hunna
12-09-2011, 05:04 AM
I am trying to write a code that runs certain macros until the last used cell in column A. The code is not working could anybody help me with this?


Sub ds()


Dim i As Integer
Dim intRowCount As Integer


intRowCount = Range("A2").CurrentRegion.Rows.Count - 1
For i = 2 To intRowCount

If Not Cells(i, 1).Value = "" Then
TextBox1.Value = Cells(i, 1).Value
TextBox2.Value = Cells(i, 2).Value
TextBox3.Value = Cells(i, 3).Value
TextBox4.Value = Cells(i, 4).Value

If Not Cells(i, 7).Value = "" Then
MsgBox "modification"

Else
MsgBox "No modification"

End If

Else
MsgBox "try again"

End If

Next i

End Sub


any help is greatly appreciated.

mdmackillop
12-09-2011, 06:04 AM
Your data may not be contiguous
Try

intRowCount = Range("A" & rows.count).end(xlup).row -1

omp001
12-09-2011, 06:09 AM
without knowing exactly what you want to get, try this way
Sub ds()

Dim i As Integer
Dim intRowCount As Integer

With ActiveSheet
intRowCount = .Range("A2").CurrentRegion.Rows.Count - 1
For i = 2 To intRowCount

If Not .Cells(i, 1).Value = "" Then
.TextBox1.Value = .Cells(i, 1).Value
.TextBox2.Value = .Cells(i, 2).Value
.TextBox3.Value = .Cells(i, 3).Value
.TextBox4.Value = .Cells(i, 4).Value

If Not .Cells(i, 7).Value = "" Then
MsgBox "modification"

Else
MsgBox "No modification"

End If

Else
MsgBox "try again"

End If

Next i
End With
End Sub

Aflatoon
12-09-2011, 06:20 AM
A couple of observations, if I may:
1. It would be helpful is you were specific about how the code fails. Do you get an error? If so, what and where? Or does it not do what you want? If so, in what way is it incorrect?
2. Do not use Integers as row counter variables. Use Long instead. Integers can only go up to 32,767 and there are a lot more rows than that in a worksheet.

hunna
12-09-2011, 09:13 AM
Thank you all for kind help.