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

 

[kyle_04]

I had a go but it didn't seem to work, would that just not insert a row?

If you look at the full VB code I am trying to find the row 1 less than the cell that contains "1 end" in column A:A, so that I can copy it and then insert.

How would it fit into the full VB code,

Regards,

Kyle

 

[kyle_04]

Solved:

p45cal formulas worked perfectly,

Thanks for all your replies.

Kyle

 

[Trevor G]

Cheers,

Errors amended but still no joy

Take a look at Andrews suggestion, it works for me when I tested it. Plus his knowledge is much wider than mine around this subject.

 

[kyle_04]

Hi,

I have adapted this formula so that it will enable me to delete the last line, vb as follows:

Sub Delete_Line1()

' Delete_Line1 Macro

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


End Sub


Now, I need to adapt this so that if the next line contains a "1" in column a, it wont allow the user to delete the line, (maybe an error message can be implemented to show that the end has been reached). I have tried to do this but im not sure how to implement an if statement into vb,

many thanks,

kyle