Hello All
Looking for help/advice on data capture VBA.
Bit of code here that works manually when not connected to BA.
It works with any cells eg H9 to AH9 or G9 to AG9.
Example here is C2 to D2.
Dim xVal As String
Private Sub Worksheet_Change(ByVal Target As Range)
Static xCount As Integer
Application.EnableEvents = False
If Target.Address = Range("C2").Address Then
Range("D2").Offset(xCount, 0).Value = xVal
xCount = xCount + 1
End If
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = Range("C2").Address Then
xVal = Target.Value
End If
End Sub
Try it out please.
I cant understand why it wont work when connected.
Any ideas ?
Excel Data Capture
-
- Posts: 3140
- Joined: Sun Jan 31, 2010 8:06 pm
I haven't got BA but if you change the lines
to
it should at least kick off the routines when connected to BA
Code: Select all
If Target.Address = Range("C2").Address Then
Code: Select all
If Not Intersect(Target, Range("C2")) Is Nothing Then
The cells are updated in blocks and you can't target a single one. Try changing it to "$C$2:$C$6"
put this into your sheet and open the immediate window
and you'll see the blocks as they arrive
put this into your sheet and open the immediate window
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
Debug.Print Target.Address
End Sub