SOLVED
Home

Correct formatting of Row parametr within a Worksheets Insert command

%3CLINGO-SUB%20id%3D%22lingo-sub-729435%22%20slang%3D%22en-US%22%3ECorrect%20formatting%20of%20Row%20parametr%20within%20a%20Worksheets%20Insert%20command%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-729435%22%20slang%3D%22en-US%22%3E%3CP%3EJust%20to%20show%20that%20I%20have%20set%20up%20wswkname%20correctly%20-%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEarly%20in%20the%20sub%20the%20following%20code%20lines%20execute%2C%20in%20the%20'initialisation'%20part%20of%20the%20sub%2C%20and%20the%20values%20work%20elsewhere%20perfectly%20well.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDim%20ws%20As%20Worksheet%3CBR%20%2F%3EDim%20wswkname%20As%20String%3C%2FP%3E%3CP%3ESet%20ws%20%3D%20ActiveSheet%3C%2FP%3E%3CP%3Ewswkname%20%3D%20ws.Name%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20read%20and%20understood%20the%20following%20example%20code%20-%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWorksheets(%E2%80%9CInsert%20row%E2%80%9D).Rows(%E2%80%9C11%3A15%E2%80%9D).Insert%20Shift%3A%3DxlShiftDown%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%2C.%20though%2C%20want%20the%20literals%2011%20and%2015%20to%20be%20variables.%26nbsp%3B%20I%20have%20assigned%20the%20necessary%20variables%20to%20two%20variables%2C%20which%20are%20named%20fxRI1%20and%20fxRI2.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAccordingly%20I%20wrote%20the%20following%20code%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDim%20fxRI1%20As%20Long%3CBR%20%2F%3EDim%20fxRI2%20As%20Long%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EfxRI1%20%3D%20sLast_Row.Value%20%2B%203%3CBR%20%2F%3EfxRI2%20%3D%20sLast_Row.Value%20%2B%204%26nbsp%3B%20'%20sLast_Row.Value%20is%20a%20form%20value%20passed%20to%20the%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWorksheets(wswkname).Rows(fxRI1%3AfxRI2).Insert%20Shift%3A%3DxlShiftDown%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20'construct'%20would%20not%20compile%20though.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECorrections%20please.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPhilip%3C%2FP%3E%3CP%3EBendigo%2C%20Victoria%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-729435%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EInsert%20Row%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EVba%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-729992%22%20slang%3D%22en-US%22%3ERe%3A%20Correct%20formatting%20of%20Row%20parametr%20within%20a%20Worksheets%20Insert%20command%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-729992%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F313780%22%20target%3D%22_blank%22%3E%40PMHunt1955%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20turned%20out%20that%20the%20statement%20needed%20some%20extra%20quotes%20as%20per%20following%20-%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWorksheets(wswkname).Rows(fxRI1%20%26amp%3B%20%22%3A%22%20%26amp%3B%20fxRI2).Insert%20Shift%3A%3DxlShiftDown%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
PMHunt1955
Contributor

Just to show that I have set up wswkname correctly -

 

Early in the sub the following code lines execute, in the 'initialisation' part of the sub, and the values work elsewhere perfectly well.

 

Dim ws As Worksheet
Dim wswkname As String

Set ws = ActiveSheet

wswkname = ws.Name

 

I have read and understood the following example code -

 

Worksheets(“Insert row”).Rows(“11:15”).Insert Shift:=xlShiftDown

 

I,. though, want the literals 11 and 15 to be variables.  I have assigned the necessary variables to two variables, which are named fxRI1 and fxRI2.

 

Accordingly I wrote the following code

 

Dim fxRI1 As Long
Dim fxRI2 As Long

 

fxRI1 = sLast_Row.Value + 3
fxRI2 = sLast_Row.Value + 4  ' sLast_Row.Value is a form value passed to the Sub

 

Worksheets(wswkname).Rows(fxRI1:fxRI2).Insert Shift:=xlShiftDown

 

This 'construct' would not compile though.

 

Corrections please.

 

Best regards

 

Philip

Bendigo, Victoria

1 Reply
Solution

@PMHunt1955 

 

It turned out that the statement needed some extra quotes as per following -

 

Worksheets(wswkname).Rows(fxRI1 & ":" & fxRI2).Insert Shift:=xlShiftDown

 

 

Related Conversations