VBA clarification required in using RANGE command

Hey all. Hope all is good.
I am a beginner in VBA and the below written one is my first code and I need some clarification from you guys. This question seems to be simple for you guys but actually I don't know how to consolidate it. And also, I don't even know whether its properly structured or not. But its working fine.

I have just shown the Vlookup part in the below code. But actually this has a lot of nested if conditions and this code has approximately 25 lines of the similar type.

My question is:
1) I have used Range("A2") & Resize(9, 1) command in all the lines.
Is there any option where I can use it only once in the beginning itself instead of writing it in all the lines. Range("A2") & Resize(9, 1) is same for all the lines.

2) In every excel workbook, only Range("A2") & Resize(9, 1) values will be varying. The rest of the commands / values will be same. Is there any option where I can save / run this code as a new feature in excel and when I run, it has to ask for entering the Range & Resize.

VBA code:
Sub Macro1()
'
' Macro1 Macro
'

'

Range("A2").Offset(0, 1).Resize(9, 1) = "=VLOOKUP(RC[-1],Database.xlsx!R1C1:R300C24,6,0)"
Range("A2").Offset(0, 4).Resize(9, 1) = "=VLOOKUP(RC[-4],Database.xlsx!R1C1:R300C24,15,0)"
Range("A2").Offset(0, 2).Resize(9, 1) = "=VLOOKUP(RC[-2],Database.xlsx!R1C1:R300C24,10,0)"
Range("A2").Offset(0, 3).Resize(9, 1) = "=VLOOKUP(RC[-3],Database.xlsx!R1C1:R300C24,13,0)"
Range("A2").Offset(0, 5).Resize(9, 1) = "=VLOOKUP(RC[-5],Database.xlsx!R1C1:R300C24,19,0)"
Range("A2").Offset(0, 6).Resize(9, 1) = "=VLOOKUP(RC[-6],Database.xlsx!R1C1:R300C24,22,0)"

End Sub

Thanks in advance for your time & suggestion.

Hi CK.
I just want to tell you that Its just simply "AWESOME". You have altered the program the way I wanted it.
Thank you for the suggestion. Thanks a lot!!!!!

If you don't mind and if you have some time, can you briefly explain me on the the below lines:
Set rng = .InputBox(Prompt:="Select starting position", Type:=8) - What is the meaning of Type:=8 ?
rowSize = .InputBox(Prompt:="Enter row size", Type:=1) - What is the meaning of Type:=1?
colSize = .InputBox(Prompt:="Enter column size", Type:=1) - What is the meaning of Type:=1 ?

Thank you once again.

Also in the same below code, can you please tell me how to paste as values.
Like, in the first seven columns, I need to paste as values and the next two columns must be in formula itself and again next five columns must be pasted as values.

Sub Demo()
Dim rng As Range
Dim colSize As Long, rowSize As Long
With Application
Set rng = .InputBox(Prompt:="Select starting position", Type:=8)
rowSize = .InputBox(Prompt:="Enter row size", Type:=1)
colSize = .InputBox(Prompt:="Enter column size", Type:=1)
End With

With rng.Resize(rowSize, colSize)
.Offset(0, 1) = "=VLOOKUP(RC[-1],Database.xlsx!R1C1:R300C24,6,0)"
.Offset(0, 4) = "=VLOOKUP(RC[-4],Database.xlsx!R1C1:R300C24,15,0)"
'So on so forth
End With
End Sub

Type:=8 means range selection (i.e. cell reference as range object). Type:=1 means numeric. See link for full list of Type argument for Application.Inputbox.
https://msdn.microsoft.com/VBA/Excel...x-method-excel

As for your last question... I'm not sure what you mean.

Hi CK. Sorry for not being clear in that case.
From the code,
.Offset(0, 1) = "=VLOOKUP(RC[-1],Database.xlsx!R1C1:R300C24,6,0)"
.Offset(0, 4) = "=VLOOKUP(RC[-4],Database.xlsx!R1C1:R300C24,15,0)"

The above 2 lines (same as above code) after running the macro will be in the formula type. But not as a Value.
But I want that to be in the "Value" type and not in the "Formula" type after running the macro.

Thanks for your time.

If HaroonSid's solution doesn't work. You can simply put value of cell back into cell.

Ex:

Please Login or Register  to view this content.