MrExcel.com - Your One Stop for Excel Tips & Solutions

Dave - In That Case - I still wasn't clear

Posted by Jim on April 20, 2001 11:48 AM

so I'll get detailed, hope you don't mind.

In the midst of a While/Wend loop, assume current sheet is named "G123456" and exists as string variable CurSht. Selected cell is J40. Workbook contains sheet named "Comp". Variable CompRow currently=2. I want to insert CurSht into Comp, Column A, Row CompRow (2). I also want to insert the contents of cell J40 into Comp, Column B, Row CompRow (2). I also want to insert a formula that involves other variables into Column C, Row CompRow.

The next loop, the current sheet is "G789123", selected cell is J40, CompRow=3, formula has changed, and they need to be inserted into Comp $A3, $B3 and $C3 respectively.

Hope this clarifies my task. Thanks, again!

Posted by Dave Hawley on April 20, 2001 11:52 AM


Jim, now you have really confused me :o) Could you post you code here ? It may be easier that way.

Dave
OzGrid Business Applications

Posted by Jim on April 20, 2001 11:58 AM

Jim, now you have really confused me :o) Could you post you code here ? It may be easier that way.

OK but this may really muck things up!

'Application.ScreenUpdating = False

'Find first active sheet
Sheets("Start").Select
ActiveSheet.Next.Select
'Find first blank row
Range("A65536").Select
Selection.End(xlUp).Select
Selection.Offset(1, 0).Range("A1").Select
ThisMonthsRow = ActiveCell.Row
Dim SixChars As String, Ref As String, CurSht As String
CompRow = 2

'Copy formulas down to new row
While ActiveSheet.Name <> " G Summary"

If n = 6 Then
Stop
End If

Range(Cells(ThisMonthsRow, 1), Cells(ThisMonthsRow, 1)).Activate
ActiveCell.Offset(-1, 4).Range("A1:P1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 7).Range("A1").Select
Application.CutCopyMode = False
'Clear Add/Red cell
Selection.ClearContents
Selection.Interior.ColorIndex = xlNone
'Set new date
ActiveCell.Offset(-2, -11).Range("A1:A2").Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A3"), Type:= _
xlFillDefault
ActiveCell.Range("A1:A3").Select
'Check Paydown, if yellow last month, change to red
ActiveCell.Offset(2, 14).Range("A1").Select
If Selection.Interior.ColorIndex = 6 Then
Selection.Interior.ColorIndex = 3
End If
ActiveCell.Offset(0, -10).Range("A1").Select
'Set Sub-Serv fee factor to 6.16 or 6.24 based on formula 12 months back
ActiveCell.Replace What:="6.24", Replacement:="6.16", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Dim AboveCell As String
AboveCell = ActiveCell.Offset(-12, -1).Address _
(Rowabsolute:=False, ColumnAbsolute:=False)
If ActiveCell.Offset(-12, 0).Formula = "=" & AboveCell & "*6.24" Then
ActiveCell.Replace What:="6.16", Replacement:="6.24"
End If
If ActiveSheet.Name <> "G" Then
If ActiveSheet.Name <> "F" Then
'Get pool number from sheet name
SixChars = Right$(ActiveSheet.Name, 6)
'Insert Add/Red value
Ref = "'N:\shared\Servicing\CountryWide Sub-Serv\First File\[" & SixChars & ".xls]DEP" & SixChars & "'!$E$10:$F$300"
ActiveCell.Offset(0, 7).Range("A1").Select
ActiveCell.Formula = "=-VLOOKUP(""Totals""," & Ref & ",2,false)"
ActiveCell.Copy
ActiveCell.PasteSpecial xlPasteValues
'Insert Loan Count value
Ref = "'N:\shared\Servicing\CountryWide Sub-Serv\First File\[" & SixChars & ".xls]SRP" & SixChars & "'!$B$5:$C$300"
ActiveCell.Offset(0, -8).Range("A1").Select
ActiveCell.Formula = "=VLOOKUP(""Totals""," & Ref & ",2,false)"
ActiveCell.Copy
ActiveCell.PasteSpecial xlPasteValues
'Insert Interest earned value
Ref = "'N:\shared\Servicing\CountryWide Sub-Serv\First File\[" & SixChars & ".xls]SUM" & SixChars & "'!$C$6:$D$25"
ActiveCell.Offset(0, -1).Range("A1").Select
ActiveCell.Formula = "=VLOOKUP(""Interest earned on deposits (from deposit report)""," & Ref & ",2,false)"
ActiveCell.Copy
ActiveCell.PasteSpecial xlPasteValues
'Insert Total Svc Fees value
Ref = "'N:\shared\Servicing\CountryWide Sub-Serv\First File\[" & SixChars & ".xls]SUM" & SixChars & "'!$C$6:$D$25"
ActiveCell.Offset(0, -1).Range("A1").Select
ActiveCell.Formula = "=VLOOKUP(""Total service fees collected (from deposit report)""," & Ref & ",2,false)"
ActiveCell.Copy
ActiveCell.PasteSpecial xlPasteValues
'Move active cell to UPB column
ActiveCell.Offset(0, 11).Range("A1").Select

'Increment counter
n = n + 1
End If
End If
ActiveSheet.Next.Select
Wend

'Processing on F Summary
Range(Cells(ThisMonthsRow, 1), Cells(ThisMonthsRow, 1)).Activate
ActiveCell.Offset(-2, 0).Range("A1:A2").Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A3"), Type:= _
xlFillDefault
ActiveCell.Range("A1:A3").Select
ActiveCell.Offset(1, 1).Range("A1:Q1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Next.Select

'Processing on G Summary
Range(Cells(ThisMonthsRow, 1), Cells(ThisMonthsRow, 1)).Activate
ActiveCell.Offset(-2, 0).Range("A1:A2").Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A3"), Type:= _
xlFillDefault
ActiveCell.Range("A1:A3").Select
ActiveCell.Offset(1, 1).Range("A1:Q1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Next.Select

'Processing on Summary
Range(Cells(ThisMonthsRow, 1), Cells(ThisMonthsRow, 1)).Activate
ActiveCell.Offset(-2, 0).Range("A1:A2").Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A3"), Type:= _
xlFillDefault
ActiveCell.Range("A1:A3").Select
ActiveCell.Offset(1, 1).Range("A1:O1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Sheets("Start").Select
ActiveSheet.Next.Select
Range(Cells(ThisMonthsRow, 1), Cells(ThisMonthsRow, 1)).Activate
Selection.Offset(0, 12).Range("A1").Select

'Indicate number of pool records imported
Dim Msg
Msg = "Finished importing " & n & " pool files."
MsgBox(Msg)

'Application.ScreenUpdating = True
End Sub

Posted by Dave Hawley on April 20, 2001 12:09 PM

Jim, There is an awful lot of selecting and Selection in there!

maybe just paste the bit your stuck on and put some code in there as a comment that would do what you want, in your own words though.

DaveOzGrid Business Applications

Posted by Jim on April 20, 2001 12:20 PM

hope this is easier to see, and yes, I'm sure there is much simpler was to do all this but not really being a VB programmer, this works.

from current sheet
1) Paste current sheet name into "Sheet500", Col A, row number = CompRow
2) Paste contents of current selected cell into "Sheet500", Col B, row number = CompRow
3) Insert a formula <a VLOOKUP> into "Sheet500", Col C, row number = CompRow

CompRow = CompRow + 1
Next Sheet
Repeat above steps 1-3

All this without ever making "Sheet500" the selected sheet

Posted by Dave Hawley on April 20, 2001 12:34 PM

1) Paste current sheet name into "Sheet500", Col A, row number = CompRow 2) Paste contents of current selected cell into "Sheet500", Col B, row number = CompRow 3) Insert a formula <a VLOOKUP> into "Sheet500", Col C, row number = CompRow Next Sheet Repeat above steps 1-3

Ok, try this

With Sheets("Sheet500")
.Range("A" & CompRow) = ActiveSheet.Name
.Range("B" & CompRow) = ActiveCell
.Range("C" & CompRow) = "=YourFormula"
End With


Dave

OzGrid Business Applications

Posted by Jim on April 20, 2001 12:36 PM

: 1) Paste current sheet name into "Sheet500", Col A, row number = CompRow : 2) Paste contents of current selected cell into "Sheet500", Col B, row number = CompRow : 3) Insert a formula <a VLOOKUP> into "Sheet500", Col C, row number = CompRow : Next Sheet : Repeat above steps 1-3

Ok, try this .Range("A" & CompRow) = ActiveSheet.Name .Range("B" & CompRow) = ActiveCell .Range("C" & CompRow) = "=YourFormula" End With Dave

so simple...! Thanks again!