Stuck with a Macro in VBA

[neil345]

Hi guys,

Im new here and I was wondering if its possible for you to help me with a Macro in VBA

Basically what Im doing is scanning barcodes, I would like to scan several barcodes at a same time as they are in the same location. However, the problem Im facing is that in some of the rooms there are not the same number of items, so I would like to tell the Macro to ignore blank cells. Also everytime I refresh it does not delete the data I have so I have to manually delete everything. Im just going to paste all the data I have in VBA.. Im new to VBA and macros so I have no idea how to fix this

'Set variables
Set wsData = Sheet2
Set wsForm = Sheet1
'find the next empty row for data input
Set rNextCl = wsData.Cells(65536, 1).End(xlUp).Offset(1, 0)
'enter the data using Offset to find next Column
'note use With....End With to let Excel that the following lines are working with rNextCl
'also reduces typing!!
With rNextCl
.Offset(0, 1).Value = wsForm.Cells(3, 2).Value
.Offset(0, 2).Value = wsForm.Cells(3, 3).Value
.Offset(1, 1).Value = wsForm.Cells(3, 2).Value
.Offset(1, 2).Value = wsForm.Cells(3, 3).Value
.Offset(2, 1).Value = wsForm.Cells(3, 2).Value
.Offset(2, 2).Value = wsForm.Cells(3, 3).Value
.Offset(3, 1).Value = wsForm.Cells(3, 2).Value
.Offset(3, 2).Value = wsForm.Cells(3, 3).Value
.Offset(4, 1).Value = wsForm.Cells(3, 2).Value
.Offset(4, 2).Value = wsForm.Cells(3, 3).Value
.Offset(1, 3).Value = wsForm.Cells(9, 2).Value
.Offset(2, 3).Value = wsForm.Cells(9, 2).Value
.Offset(3, 3).Value = wsForm.Cells(9, 2).Value
.Offset(4, 3).Value = wsForm.Cells(9, 2).Value
'//offset only cells with values

End With
'confirm data transferred
MsgBox "List Updated" & vbCrLf & "", vbInformation, "Data transfer"
'clear input cells, note use of Named Range
Dim c As Range
For Each c In wsForm.UsedRange
If c.Locked = False Then
c.ClearContents
wsForm.Range("B2").Select
End If
Next



'clear variables
Set wsData = Nothing
Set wsForm = Nothing
Set rNextCl = Nothing
End Sub



Many Thanks for any assistance,

Regards,
Juan M

 

[patel45]

can you attach a sample file ?
please upload it on a share site then paste the link here

 

[neil345]

can you attach a sample file ?
please upload it on a share site then paste the link here

Here is the link.Thankyou very much for your help

https://guerlainaldomonasterios.sharefile.com/d/sc2bbfb9e27a4ebcb

Regards,
JuanM

 

[patel45]

I did not understand your code, I need a sample file with data and desired result

 

[neil345]

I did not understand your code, I need a sample file with data and desired result

https://guerlainaldomonasterios.sharefile.com/d/sf2087b6ddcd48dca


this is the samplefinal with mydesire result...Ihope this is enough information and apologies for the missunderstanding,Im new in excel and learning

Many Thnaks for your help

 

[rpaulson]

How about this

Sub Move_data()
'Set variables
Set wsdata = Sheet2
Set wsform = Sheet1
'find the next empty row for data input

'enter the data using Offset to find next Column
'note use With....End With to let Excel that the following lines are working with rNextCl
'also reduces typing!!
For r = 4 To 8
rNextCl = wsdata.Cells(65536, 1).End(xlUp).Row + 1
If wsform.Cells(r, "B") = "" Then GoTo 100 'slip if blank

wsdata.Cells(rNextCl, "A") = wsform.Cells(r, "B").Value
wsdata.Cells(rNextCl, "B") = wsform.Cells(r, "C").Value
wsdata.Cells(rNextCl, "C") = wsform.Range("B10")
wsdata.Cells(rNextCl, "D") = wsform.Range("B11")

100 Next r

'confirm data transferred
MsgBox "List Updated" & vbCrLf & "", vbInformation, "Data transfer"
'clear input cells, note use of Named Range
Dim c As Range
For Each c In wsform.UsedRange
If c.Locked = False Then
c.ClearContents
wsform.Range("B2").Select
End If
Next



'clear variables
Set wsdata = Nothing
Set wsform = Nothing
Set rNextCl = Nothing
End Sub

 

[neil345]

How about this

Sub Move_data()
'Set variables
Set wsdata = Sheet2
Set wsform = Sheet1
'find the next empty row for data input

'enter the data using Offset to find next Column
'note use With....End With to let Excel that the following lines are working with rNextCl
'also reduces typing!!
For r = 4 To 8
rNextCl = wsdata.Cells(65536, 1).End(xlUp).Row + 1
If wsform.Cells(r, "B") = "" Then GoTo 100 'slip if blank

wsdata.Cells(rNextCl, "A") = wsform.Cells(r, "B").Value
wsdata.Cells(rNextCl, "B") = wsform.Cells(r, "C").Value
wsdata.Cells(rNextCl, "C") = wsform.Range("B10")
wsdata.Cells(rNextCl, "D") = wsform.Range("B11")

100 Next r

'confirm data transferred
MsgBox "List Updated" & vbCrLf & "", vbInformation, "Data transfer"
'clear input cells, note use of Named Range
Dim c As Range
For Each c In wsform.UsedRange
If c.Locked = False Then
c.ClearContents
wsform.Range("B2").Select
End If
Next



'clear variables
Set wsdata = Nothing
Set wsform = Nothing
Set rNextCl = Nothing
End Sub

Wow thankyou very much it works perfectly. One thing is that when I click the update button it does not run the macro,but when I go to visual basic editor and I run it; it works fine.. how can I fix that?

Thanks for the help