Forum Discussion
couple problems. I know it's somewhere in my spreadsheet
My spreadsheet is a basic inventory sheet. It starts with an amount and you can subtract amounts and it gives you the remaining amount. It also copies changes onto a second worksheet.
Anyway, my issue ( not a big issue but nontheless), is that when you enter an amount under "used footage" and hit enter, it does not go down to the next row in the column automatically.
Also, ( not a big issue) is that I have intentional blank cells in column "TYPE" that can be manually entered with text. If I leave those cells blank it kind of screws up my inventory worksheet ( "CopperHistory").
Is there a way to enter a formula so that the blank cells in column A ( TYPE) fill in with some text automatically? It can be any text as long as anytime there is a blank cell in column A it fills it in with something.
attached spreadsheet to understand what I'm saying because I'm an idiot.
Hi there
Based on what I understand, maybe give this modified file a shot?
In answer to your first question, just press enter twice to move down to the next row.
Question two, I've set it up so that when you enter a TYPE into any cell in column A, it will automatically be reflected in the Copper History worksheet. I've aligned the CopperHistory worksheet content to your Copper worksheet content.
Essentially you can link between worksheets by going to your Copper History worksheet and clicking into a cell (e.g. A4), press =, click onto the Copper tab, and click onto the cell that you want to reflect. Press Enter when done.
Hopefully this is the solution to your query?
Please let me know how you go?
Cheers
Damien
Hey Damien. Thanks for the reply.
I understand the double click enter. That's good.
However, the spreadsheet you modified really doesn't do what I was hoping.
Just looking for a way to fill a range of blank cells in column A with some text automatically.
One more question for you.If you look at my original spreadsheet try to insert a new row on worksheet "copper". It gives a debug error that I don't know how to correct.
Thanks for your help
Hi there
I just need to understand a bit more about the blank cells in Column A filling with text automatically.
Are you just looking for placeholder text?
1. If so, just type whatever text you want into a cell in column A.
2. Copy the cell.
3. Click once onto a blank cell where you want the text to begin from.
4. Press Ctrl + Shift + Down to select all cells down to 65k.
5. Ctrl + v to paste.
All cells will have text. You can just replace the text when you are ready to do so.
I've attached an updated version of the file and have formatted both sheets so you don't need to add new lines, you just have to type in the values and away you go.
This may fix the issue with inserting lines that you have mentioned.
If I haven't understood you right, please give me more details on the column A text thing so I can get a better idea.
Let me know how you go?
Cheers
Damien
I looked over your sheet and made some slight changes to the VBA code. Basically replace your code starting from "On Error Resume Next" with the following code:
On Error Resume Next
Application.ScreenUpdating = False
If Not Intersect(Target, Range("C:C, A:A, F:F")) Is Nothing Then
Dim r2 As Integer
r2 = Application.WorksheetFunction.CountA(Sheets("CopperHistory").Range("C:C"))
'Not necessary to create a variable for each column since each new entry will be on the same row no matter which column it's in
Sheets("CopperHistory").Range("B2").Offset(r2).Value = Sheets("Copper").Range("A" & Target.Row).Value
Sheets("CopperHistory").Range("C2").Offset(r2).Value = Sheets("Copper").Range("C" & Target.Row).Value
Sheets("CopperHistory").Range("D2").Offset(r2).Value = Sheets("Copper").Range("F" & Target.Row).Value
End If
Application.ScreenUpdating = True
On Error GoTo 0
'End Sub
Selection.Offset(1, 0).Select'This will advance your target to the next row
End Sub
