Looping through a named range 1

[CTKC]

Hello,

I am new to VBA (and the learning process is going slow becuase of work hours) but I need help putting together a loop to go through a named range. My code is as follows:

Sub test()

Dim intlastrow As Integer
Dim CN As String
Dim lrow As Integer



 intlastrow = Cells(Rows.Count, 2).End(xlUp).Row
     MsgBox intlastrow

For lrow = intlastrow To 2 Step -1
    With Range("WBS").Select
        If Not Range(lrow, 2).Value = "" Then
            Range(lrow, 1).Value = MID(Trim(Cells(lrow, 2)), 9, 12)
        End If
    End With
Next lrow
End Sub

I am getting an error on the If statement (1004 run time error). Maybe i am going about this the wrong way, but my method behind this is if the range (lrow,2) value is not blank then do the forumula. Any help would be appreciated.

Thanks.

 

[SkipVought]

Hi,

Make lRow LONG. Your With Range("WBS").Select does nothing constructive.

and check the if...

Sub test()
    Dim intLastRow As Integer
    Dim CN As String
    Dim lRow As Long
    
    intLastRow = Cells(Rows.Count, 2).End(xlUp).Row
    MsgBox intLastRow
    
    For lRow = intLastRow To 2 Step -1
'        With Range("WBS").Select[b]
            If Range(lRow, 2).Value <> "" Then[/b]
                Range(lRow, 1).Value = Mid(Trim(Cells(lRow, 2)), 9, 12)
            End If
'        End With
    Next lRow
End Sub

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[PeteG]

Hi,

I prefer looping through a named range like this:

dim currCell as Range

for each currCell in Range("WBS")
    'Do stuff in here like testing currCell.Value
next

 

[PHV]

Something like this (typed, untested)?

Sub test()
Dim c As Range
For Each c In Range("WBS").Columns(2).Cells
  If c.Value <> "" Then c.Offset(0, -1) = Mid(Trim(c.Value), 9, 12)
Next
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Bong]

First of all, you don't need select.
Second, although you use with range("WBS"), you don't seem to be doing anything with it (as in .cells[/b] or some such).
Third, your range(rownumber,columnnumber) notation is wrong. You want cells(rownumber,columnnumber) perhaps.

Perhaps something like this:

with range("WBS")
  for lrow = inlastrow to 2 step -1
   if not .rows(lrow).columns(2) = "" Then
     .rows(lrow).columns(1) = MID(Trim(.rows(lrow).columns(2)), 9, 12)
   end if
  next
end with

_________________
Bob Rashkin

 

[SkipVought]

How did I miss the RANGE thing???

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[CTKC]

Thanks for all the help.

Another question:

The number of rows are going to be variable.

intlastrow = Cells(Rows.Count, 2).End(xlUp).Row
  msgbox intlastrow

[b]Range("A2:I& intlastrow").Name = "DataItems"[/b]

The bold portion does not do anything. Is it becasue intlastrow is not a numerical number?

Thanks again.

 

[SkipVought]

Range("A2:I" & intlastrow)

Turn on your macro recorder and NAME a range in the NAME BOX. Turn off your macro recorder and observe your code for NAMING a range.

Post back if you need help.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[Bong]

Range("A2:I" & intlastrow).Name = "DataItems"

_________________
Bob Rashkin

 

[SkipVought]

I just learned something that I should have realized; that naming a range is that simple.

Thanx CTKC! ==> *

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[CTKC]

Thanks Skip.

I am having a problem that I don't seem to know why it's happening. I have 3 users forms. In ThisWorkbook i have a sub that when the workbook opens frmStart will appear (frmstart.show vbmodeless). Then once you click the start command button the code is inside there. First few lines of code is:

Dim intlastrow As Long
Dim lrow As Long
Dim c As Range
Dim wbProp, wbActuals As Workbook
Dim weekending As Date


[b]frmStart.Hide[/b]
frmDDGProp.Show vbModeless

newpath = Application.GetOpenFilename("Excel, *xls")

frmDDGProp.Hide

I get an error when I compile the code, it says "Method or data member not found" where I hide frmStart. This has not happened to me before (I only wrote about 3 macros thus far). When i type frmstart. and the options come up, Hide is not one of them for whatever reason. If anyone could share why this is happening it would be apprecaited.

Thanks.

 

[SkipVought]

Remarks

When a UserForm is modal, the user must supply information or close the UserForm before using any other part of the application. No subsequent code is executed until the UserForm is hidden or unloaded. Although other forms in the application are disabled when a UserForm is displayed, other applications are not.

When the UserForm is modeless, the user can view other forms or windows without closing the UserForm.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}