Solved: Variable Help

[YellowLabPro]

The variable LR is confusing me.... imagine that :dunno

Finding the LRow, what is LR in LRow =LR(WS1,1)
This was provided by our guru here and he is not online now....


Sub CreateParent()
Dim WS1 As Worksheet
Dim i As Integer
Dim LRow As Long
Set WS1 = Workbooks("TGS Item Record Creator.xls").Sheets("Record Creator")
LRow = LR(WS1, 1)
WS1.Range("aa5:ab" & LRow).Copy
WS1.Range("aa5:ab" & LRow).PasteSpecial Paste:=xlPasteValues
For i = Cells(Rows.Count, "D").End(xlUp).Row To 3 Step -1
If Cells(i, "D") = "NEED PARENT" Then
Cells(i, "D").EntireRow.Insert
Cells(i + 1, "D").EntireRow.Copy Destination:=Cells(i, 1)
Cells(i + 1, "D").Formula = "=IF(LEFT(W" & i + 1 & ",2)=""~P"","""",IF(AND(LEFT(W" & i + 1 & ",2)=""~C"", _
LEFT(U" & i + 1 & ",8)<>LEFT(U" & i & ",8)),""NEED PARENT"",""""))"
Cells(i + 1, "D").Copy Destination:=Cells(i, "D")
Cells(i + 0, "N").ClearContents
Cells(i + 0, "Z").Value = 0
Cells(i + 0, "AC").Value = 1998
End If
Next i
WS1.Calculate
[w5].Activate
End Sub

[clvestin]

LR is a called function, ie somewhere in the routine is a

Function LR(wk as worksheet, something as something)

This must a function used to determine the last row on the worksheet or within the working range.

[YellowLabPro]

Ok.... Just when I thought it was safe to go outside.....

[clvestin]

I'm lacking an idea of what range needs to be searched for the last row.
However, the lines below ,substituted for the function call(comment it out), and using an appropriate range might let you see what the routine can or should do.


Set rowobject = Range("b5:b36").Cells _
.SpecialCells(xlCellTypeBlanks).End(xlUp)
rowrng = rowobject.Row
WS1.Range("aa5:ab" & rowrange).Copy
WS1.Range("aa5:ab" & rowrange).PasteSpecial Paste:=xlPasteValues

[lucas]

set for LR seems to be missing for one thing, which is what clvestin is pointing out...sure that's all of the code?

[YellowLabPro]

New Project's code


Sub Find_NA()
Dim WsS As Worksheet
Dim WsD As Worksheet
Dim i As Integer
Dim LRow As Long
Set WsS = Workbooks("MasterImportSheetWebStore.xls").Sheets("PCCombined_FF")
Set WsD = Workbooks("MasterImportSheetWebStore.xls").Sheets("NA_Fix")
LRow = LR(WsS, 1)
For i = Cells(Rows.Count, "AB").End(xlUp).Row To 1
If Cells(i, "AB") = "#N/A" Then
Cells(i, "AB").EntireRow.Copy Destination:=WsD.Range(1, 1)
Cells(i + 1, "D").EntireRow.Copy Destination:=Cells(i, 1)
End If
Next i
End Sub

[YellowLabPro]

Hey Steve,
Yep that is all the code...
Here is yet another example of working code by the same author


Sub FillDown()
Dim WS1 As Worksheet
Dim LRow As Long
Dim c As Range
Set WS1 = Workbooks("TgsItemRecordCreatorMaster.xls").Sheets("Record Creator")
LRow = LR(WS1, 8)
With WS1
.Range("A4:G" & LRow).FillDown
.Range("Q4:Q" & LRow).FillDown
.Range("T4:Y" & LRow).FillDown
.Range("AA4:AB" & LRow).FillDown
.Range("AD4:AE" & LRow).FillDown
.Calculate
End With
' For Each c In Cells.SpecialCells(xlCellTypeFormulas)
' WS1.Cells(5, 28) = c.Value
' Next c
'WS1.Range("AB5" & LRow).Copy WS1.Range("AB5" & LRow).PasteSpecial _
Paste:=xlPasteValues
End Sub

[YellowLabPro]

I am not sure what it means and how it works yet, but here is something:

Highlighted intellisense: "A4:G" & LRow LR(WS as worksheet, Col) as long
Highlighted intellisense: & LRow Local LRow as long

[lucas]

doesn't run for me...stops at LR and says sub or function not defined.

[YellowLabPro]

I experience the same thing on New Project, but on the last example runs w/ no problem.
I will try and put a scaled down version of the file up. Give me a couple of minutes to put it together.

[johnske]

There is a function that's been omitted here - what's the link to the original post where this came from?

[clvestin]

If the last example runs, then Function LR has to be somewhere.
Without a further reference, it must be in the current module. Or--perhaps,like a property, stored in a class module. Poke around

[YellowLabPro]

See Attachment

Run using Create Parent button

[lucas]

Create parent doesn't run for me....same problem

[lucas]

TGSItemRecordCreatorMaster.xls
could it be in this file or your personal.xls?

[johnske]

It's not in there (TGSItemRecordCreatorMaster2) - looks like you'll have to wait till Malcolm's back on deck to see his intent here :o)

[YellowLabPro]

Got it. Trying to make the file small enough to upload, I was deleting all modules and stumbled onto this....


'Last Row
Function LR(WS As Worksheet, Col As Variant) As Long
Application.Volatile
If Not IsNumeric(Col) Then Col = Columns(Col).Column()
LR = WS.Cells(Rows.Count, Col).End(xlUp).Row
End Function

[lucas]

So its calling the function LR with the parameters layed out in the first line of the function... the worksheet and column

Function LR(WS As Worksheet, Col As Variant) As Long


LRow =LR(WS1,1)
WS1 is the worksheet and 1 is the column

[lucas]

LRow could also be used something like this without the function.


LRow = Sheets("Record Creator").Cells(Rows.Count, Col).End(xlUp).Row

Malcolm has a reason to use the function though because it checks a couple of other things and also as a function it can be called from numerous routines just by using the call with the parameters:

LR(WS1,1)

[YellowLabPro]

clvestin:

However, the lines below ,substituted for the function call(comment it out), and using an appropriate range might let you see what the routine can or should do.

Following clvesin's approach and luca's too, the results from the code below; nothing runs. It looks ok to me.... any ideas where to look?

thanks


Option Explicit

Sub Find_NA()
Dim WsS As Worksheet
Dim WsD As Worksheet
Dim i As Integer
Dim LRow As Long
Set WsS = Workbooks("MasterImportSheetWebStore.xls").Sheets("PCCombined_FF")
Set WsD = Workbooks("MasterImportSheetWebStore.xls").Sheets("NA_Fix")
'LRow = LR(WsS, 1)
LRow = WsS.Cells(Rows.Count, 1).End(xlUp).Row
For i = Cells(Rows.Count, "AB").End(xlUp).Row To 1
If Cells(i, "AB") = "#N/A" Then
Cells(i, "AB").EntireRow.Copy Destination:=WsD.Range(1, 1)
End If
Next i
End Sub

[Norie]

YLP

What do you mean by nothing runs?

I can see at least one problem with the code, an unreferenced Cells.

[YellowLabPro]

For better clarification:
There is no activity in the worksheet. I did mark the module for debugging, the code runs to line the following line and stops, no errors provided to track down:
For i = Cells(Rows.Count, "AB").End(xlUp).Row To 1

[Norie]

Like I said unreferenced Cells.:)

[YellowLabPro]

Norie,
I need help w/ that. It may be obvious to you, but to me it is still a foreign language... can you help me spot where I need to fix?

[Norie]

YLP

You need to reference a worksheet when you use Cells otherwise Cells will refer to the active sheet, and that might not be the one you want.

[YellowLabPro]

I see your point. But not able to figure out how to reference it yet, if is the activesheet, should the program deliver the desire results as it is written?

[lucas]

Hi YLP,
I would go back to using the function that Malcolm gave you. It does more than find the last row and it can be used across multiple routines. Also both of the workbooks have to be open when you run it as I see no code for opening a workbook.

[YellowLabPro]

I would love too-- but since I am not picking up how to use that I am stuck either way....:dunno

[mdmackillop]

Hope this helps explain. The LR Function shortcuts repeated entry of the usual Cells(Rows.Count,1).End(xlUp)
Option Explicit
Sub Find_NA()
Dim WsS As Worksheet
Dim WsD As Worksheet
Dim i As Long
Dim LRow As Long

Set WsS = Workbooks("MasterImportSheetWebStore.xls").Sheets("PCCombined_FF")
Set WsD = Workbooks("MasterImportSheetWebStore.xls").Sheets("NA_Fix")
'Use the function to get the row number of the last cell in column 1 of
'the specified sheet.
LRow = LR(WsS, 1)
'so this line is not required
'LRow = WsS.Cells(Rows.Count, 1).End(xlUp).Row

'In this bit, unless you specifically tell Excel where the Cells are,
'i will be set the the row number of the last cell in column AB in the
'active sheet, which may not be either of your referenced sheets

'This line is like saying 'For i = 100 to 1' If you want to do this in
'reverse order then you need 'For i = 100 to 1 Step -1'

For i = Cells(Rows.Count, "AB").End(xlUp).Row To 1
'This line returns an error
If Cells(i, "AB") = "#N/A" Then
'Because the destination does not change here, the code will
'overwrite all copied rows to Row 1 on sheet WsD
Cells(i, "AB").EntireRow.Copy Destination:=WsD.Range(1, 1)
End If
Next i

'It looks like you want to copy from WsS to WsD

'Use the LR function to get the last row number of AB in WSS
'also use it to get the last row for the destination, and offset it
'to avoid overwriting

'I've assumed ascending order
For i = 1 To LR(WsS, "AB")
If Not Application.WorksheetFunction.IsNA(WsS.Cells(i, "AB")) Then
WsS.Rows(i).Copy Destination:=WsD.Cells(LR(WsD, 1) + 1, 1)
End If
Next i

End Sub


'Last Row
'You need to pass the worksheet variable and the designated column
'either the number or the letter.

Function LR(WS As Worksheet, Col As Variant) As Long
If Not IsNumeric(Col) Then Col = Columns(Col).Column()
LR = WS.Cells(Rows.Count, Col).End(xlUp).Row
End Function

[YellowLabPro]

Thanks MD!
I ran the code on the worksheet you uploaded. I have not studied the code too hard yet. But in running it, it copies over more than just the records w/ the "#N/A" in col. "AB"

If you see something I don't, will you let me know?

thanks....

YLP

[mdmackillop]

Because I got it the wrong way round!!!
Change
If Not Application.WorksheetFunction.IsNA(WsS.Cells(i, "AB")) Then

to

If Application.WorksheetFunction.IsNA(WsS.Cells(i, "AB")) Then

[YellowLabPro]

Magnificent!
Well as I said in an earlier post, just when I thought it was safe to go back in the water..... :bug: This is good though, keeps me sharp.


Thanks for piecing it together- I will have to study the code for a while--