VBA Code assistance please

[Chissa]

Hello gurus!

I wonder if you can help me? I have the below code, however the line that I have highlight works but basically I want the VLOOKUP value (B2) formula to change consistently with whatever line the data has been put on on the summary page.....I hope I have made myself clear enough and someone can help..!

Thank you very much


Sub NewLoanbutton()

Dim WSName As String
Dim WSheet As Worksheet
NextRow = Range("A" & Rows.Count).End(xlUp).Row + 1

WSName = InputBox("Please enter then name of the person you wish to create a new loan for")
If WSName = "" Then Exit Sub

If SheetExists(WSName) Then
MsgBox ("A loan for " & WSName & " already exists. Please use the Search button to locate this page.")
Application.Goto Worksheets("Summary").Range("A1")
Exit Sub
Else
'Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Template").Select
Sheets("Template").Copy After:=Worksheets(Worksheets.Count)
Range("A10").Select
Sheets("Template (2)").Select
ActiveSheet.Name = WSName
End If



'Headings
Sheets("Template").Select
Cells.Select
Selection.Copy
Sheets(WSName).Select
Cells.Select
ActiveSheet.Paste
Application.CutCopyMode = False

'New Loan box

NewLoanForm.Show

Sheets("Summary").Select
Nextrow1 = Range("B" & Rows.Count).End(xlUp).Row + 1

Range("B" & Nextrow1).Value = WSName
Range("D" & Nextrow1).Formula = "=SUMIF( '" & WSName & "'!B:B, ""<2011"", '" & WSName & "'!G:G)"
Range("E" & Nextrow1).Formula = "=SUMIF( '" & WSName & "'!B:B, ""=2011"", '" & WSName & "'!G:G)"
Range("F" & Nextrow1).Formula = "=SUMIF( '" & WSName & "'!B:B, ""=2012"", '" & WSName & "'!G:G)"
Range("G" & Nextrow1).Formula = "=SUMIF( '" & WSName & "'!B:B, ""=2013"", '" & WSName & "'!G:G)"
Range("A" & Nextrow1).Formula = "=VLOOKUP(B12,Information!A:C,3,FALSE)"

End Sub

 

[vw412]

First of all, one of the rules of this forum is that you put code between Code tags so it gets formatted to be more readable. You should edit your post and put those tags in properly.

I think I know your question but need to ask what variable will determine the cell reference in the VLOOKUP? You are using Nextrow1 to set the row in your Range; should that also be used to set the row in the VLOOKUP?

 

[Chissa]

Apologies I did not realise. Noone has mentioned it before...are you able to tell me how to tag it? I clicked on edit tags but I'm not sure what I am supposed to do...sorry.

Yes the Nextrow1 takes the vlookup to the relevant row and column which relates back to the variable of "WSname" this is typed in at the beginning eg: P Green, I can't quite work out how to get the look up to lookup on the "WSName" variable everytime I write it in the formula it returns the value #Name.

Thank you for your help...and sorry again about the edit tags....I will see if I can research this.

Thank you

 

[Joe4]

First of all, one of the rules of this forum is that you put code between Code tags so it gets formatted to be more readable. You should edit your post and put those tags in properly.

vw12,

It is recommended, but it is not a rule!

The rules can be found here: http://www.mrexcel.com/forum/showthread.php?99490-Forum-Rules,
and be careful not tread into issues with rules 15 and 16!

 

[vw412]

My apologies for stepping on some toes. I will be careful not to impersonate a moderator in the future.

 

[vw412]

I think you want to do something like this:

[B]Range("A" & Nextrow1).Formula = "=VLOOKUP(" & WSName & ",Information!A:C,3,FALSE)"
[/B]

This is assuming you are looking for WSname in the table at Information!A:C.

 

[finaljustice]

Hi,
I have recently been working with a project that I have to input formulas into cells like you have been attemping, at first sight vw412 seems to have the right idea for you. If I may I have a little tweaking that I think that could be useful for you. Stating in formulas to search in entire columns can slow down your workbook. What I do is look what is the bottom row of where my calculations must extend and then place in the formulas just in the desired range.
Here is part of my code with what I have described.

x = ActiveWorkbook.Sheets.CountSheets(x).Select
ws = ActiveSheet.Name
blimit = Range("D1048576").End(xlUp).Address  'THIS IS WHERE THE "TRICK" IS. GETTING THE BOTTOM LIMIT OF YOU RANGE
blimit = Right(blimit, 3) 'TRIMMING IT TO STAY WITH ONLY THE ROW NUMBER
Sheets(1).Select
Range("A27").Select
Do While ActiveCell = Empty
varlookup = ActiveCell.Address
linha = Trim(Right(varlookup, 2))
Range("$D$" & linha).Formula = "=Iferror(VLookup(" & varlookup & ", " & ws & "!$D$6:$AR$" & blimit & ", 2, False),"""")" 
Range("$K$" & linha).Formula = "=Iferror(VLookup(" & varlookup & ", " & ws & "!$D$6:$AR$" & blimit & ", 31, False),"""")" 
Range("$L$" & linha).Formula = "=Iferror(VLookup(" & varlookup & ", " & ws & "!$D$6:$AR$" & blimit & ", 8, False),"""")"
Range("$M$" & linha).Formula = "=Iferror((VLookup(" & varlookup & ", " & ws & "!$D$6:$AR$" & blimit & ", 7, False)*$K$" & linha & "),"""")"
Range("$S$" & linha).Formula = "=Iferror(Vlookup(" & varlookup & ", " & ws & "!$D$6:$AR$" & blimit & ",40,False),"""")" 
Range("$T$" & linha).Formula = "=Iferror(Vlookup(" & varlookup & ", " & ws & "!$D$6:$AR$" & blimit & ",41,False),"""")" 
Range("$U$" & linha).Formula = "=Iferror(If($B$" & linha & " ="""",$S$" & linha & "-$C$" & linha & ",$S$" & linha & "-$B$" & linha & "*6),"""")"
ActiveCell.Offset(1, 0).Select
Loop

Ok I hope you understand what I mean, just making variables populated by the limits of the desired range.
Hope this helps,
FJ

 

[vw412]

Finaljustice,

Thanks for your post. It is this kind of conversation about a particular issue that helps all of us.

vw412