0

I have a Access database that I am getting data from, I added a col right next to the data.I use this col to add some data to it manually. Rows are added to the top of the table and everytime rows are added the manual data col and the Db data get misaligned?

since i am not too techy this is what could come up with

Sub addcells()

x = Worksheets("SaleRates").Range("F:f")
xrows = Application.WorksheetFunction.count(x) + 1
Range("o2") = xrows - Range("O1")
insrow = Range("O2").Value

If Range("O2").Value > 0 Then

Range("Z1:Z" & insrow).Select
Selection.Copy
Range("M2").Select
Selection.Insert Shift:=xlDown

End If

End Sub

I just call this sub using a worksheet_change event in the required worksheet. it does not work because it cant insert cells in table. This works if i resize table and remove the manual col from it but it does nto work when data is modified or deleted

braX
  • 11,506
  • 5
  • 20
  • 33
Hussain Halai
  • 9
  • 2
  • 1
    You should read ["How to avoid using Select in VBA"](https://stackoverflow.com/q/10714251/14608750), which explains and tackles one of the most common problems that new coders have with VBA macros. In your code, reliance on the `Selection` object will cause frequent errors. – Toddleson Dec 22 '22 at 15:14
  • oh, i did not make this code. I used OpenAi's ChatGPT or this solution/workaround.... – Hussain Halai Dec 23 '22 at 04:58
  • `Count` only counts numbers - if column F contains text (including numbers formatted as text) that won't be included in the count. I guess ChatGPT doesn't know that. Other than that I don't understand the question - you have a data table copied into Excel and when you add rows it gets misaligned with the original table in Access? It'll do that as you're adding data in Excel and not updating Access? Did you add a column next to the data in Excel or Access? All very confusing.... draw us a picture. :) – Darren Bartrup-Cook Jan 03 '23 at 15:53

0 Answers0