Visual Basic

[kyle_04]

Hi,

I am trying to create a macro to insert a new row in a protected sheet,

I am trying to insert a match function into VB to find the last row on the worksheet and came accross ".worksheet function".

Now, I have seemed to get my head around it up until I insert some text (&":"&). The formula works because I have tried it in excel. Formula in VB as follows:

Rows.Application.WorksheetFunction.MATCH("1 end",worksheet(timesheet).range("A:A"),0).value-1&":"&WorksheetFunction.MATCH("1 end",worksheet(timesheet).range("A:A"),0).value-1.Select

On another note is there a easier way of finding a row number than using two matches i.e. so it returns 1:1 (For example), formula in excel as follows =(MATCH("1 end",A:A,0))-1&":"&(MATCH("1 end",A:A,0))-1

Many thanks,

Kyle

 

[Ruddles]

I'm sorry, I must be having a senior moment because I'm not following what you're trying to do. Sorry!

If you want to know how many rows have been used in your worksheet, you could use something like:-

sheets(1).usedrange.rows.count

You may have to take account of where it starts if there are any blank rows at the top. The top row of the used range is:-

sheets(1).usedrange.row

The last used cell in column A would be:-

sheets(1).cells(sheets(1).rows.count,"a").end(xlup).row

 

[Andrew Poulsom]

Try this:

Sub Test()
    With Worksheets("timesheet")
        Range("A" & Application.Match("1 end", .Columns(1), False)).EntireRow.Insert
    End With
End Sub

 

[Trevor G]

I'm not the expert here, but you would need to use Worksheets("A NAME") rather than Worksheet(A NAME).

Also wouldn't you need spaces ).value-1 &":"&

 

[p45cal]

try:
to select:

Worksheets("timesheet").Columns(1).Find(What:="1 end", searchdirection:=xlPrevious, lookat:=xlWhole).Offset(-1).EntireRow.Select

or to insert a line too (a guess at where you want to insert):

Worksheets("timesheet").Columns(1).Find(What:="1 end", searchdirection:=xlPrevious, lookat:=xlWhole).EntireRow.Insert

these don't quite match your formula in that if there are two instance of '1 end' in column A, your version found the upper one, this finds the last one.

To change this to mirror your code, change xlPrevious instances to xlNext.

 

[kyle_04]

Apologies my VB skills are not that great, and I seem to have forgotten most of it from college as I hardly use it.

In a nutshell I am trying to insert a copied line when the document is protected so the user cannot make errors etc. Therefore for the line to be inserted I need to find the last row, which in this case is 1 less than a cell containing "1 end" in column A:A.

I'll give you the whole VB to give you an idea of what Im trying to do:

Sub New_Line1()

Sheets("timesheet").Unprotect "password"
'
' New_Line1 Macro
'

'
Range("A1").Select
Cells.Find(What:="1 end", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
Rows.Application.WorksheetFunction.MATCH("1 end",worksheet("timesheet").range("A:A"),0).value-1 &":"&WorksheetFunction.MATCH("1 end",worksheet("timesheet").range("A:A"),0).value-1.Select
Selection.Copy
Rows("30:30").Select
Selection.Insert Shift:=xlDown


Sheets("timesheet").Protect "password"
End Sub


Trevor I have now amended to Worksheets("A NAME") and the inserted the space, this seems to get it to the end of the formula but still shows " compile error"

Regards,

Kyle

 

[kyle_04]

Once I have got this one correct I can apply to

Rows("30:30").select

Cheers,

Kyle

 

[Trevor G]

I still see small errors here

Rows.Application.WorksheetFunction.MATCH("1 end",worksheet("timesheet").range("A:A"),0).value-1 &":"&WorksheetFunction.MATCH("1 end",worksheet("timesheet").range("A:A"),0).value-1.Select

Would be

Rows.Application.WorksheetFunction.MATCH("1 end",worksheets("timesheet").range("A:A"),0).value-1 &":"&WorksheetFunction.MATCH("1 end",worksheets("timesheet").range("A:A"),0).value-1.Select

 

[kyle_04]

Cheers,

Errors amended but still no joy

 

[Andrew Poulsom]

Did you try what I suggested?