 You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
I'm writing a workbook with an input sheet, which transfers text and other data over to the first blank row of a log sheet. That bit works fine. I then want to put borders around the cells of the new data, but the selecting the cells to format fails every time despite using the same technique that I've used to copy data from one sheet to another - any ideas?
The bold row fails, reporting a Run-time error 1004, Select method of range class failed. Ultimately I want to select cells A to O on the 'num' row, but even a single selection fails.
ActiveWorkbook.Sheets("Local Action Log").Range("L" & num).Value = ActiveWorkbook.Sheets("New Action").Range("O10").Value
ActiveWorkbook.Sheets("Local Action Log").Range("M" & num).Formula = "=(K" & num & "*L" & num & ")"
'Formatting in new action line. Borders, wrap text and cell shading.
[b] ActiveWorkbook.Sheets("Local Action Log").Range("A" & num).Select
[/b] Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Flummoxed. I've tried all sorts of combinations
Why not just set the border directly without doing the select first?
Yep, tried that. The first step in my VBA is to count previous used rows, which generates the 'num' function to define which blank row on the local action log sheet to populate.
Any kind of formatting applied first, even borders makes the VBA count that row as used. In that case if I have 300 bordered rows, even with no text in them the data goes into row 301.
I'm not sure why you can't set it directly. You have already calculated 'num' so it should already be pointing at the correct line. You then just set the border directly
With ActiveWorkbook.Sheets("Local Action Log").Range("A" & num)
.Borders(xlDiagonalDown).LineStyle = xlThin
'Whatever other formatting you want to do
End With
I suspect we are missing an actual step taken in your efforts to describe the problem succinctly.
Yep, tried that. The first step in my VBA is to count previous used rows, which generates the 'num' function to define which blank row on the local action log sheet to populate.Any kind of formatting applied first, even borders makes the VBA count that row as used. In that case if I have 300 bordered rows, even with no text in them the data goes into row 301.
easy, fix, just loop through rows looking for last non blank one...
leffeboy - with a bit of manipulation, that worked! Marvellous. Cheers.
Excellent 🙂
For info using Select is generally a great way of slowing code down and making it longer. It's rarely needed.