Checking to see if data already exists before appending or adding

stirlingmw1

Board Regular
Joined
Jun 17, 2016
Messages
50
Office Version
  1. 2016
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
I have this piece of code that adds data to the next available column on worksheet(WSName1)

VBA Code:
With Worksheets(WSName1)
                        .Cells(1, LastColumn).Value = CmboUnit.Value
                        .Cells(2, LastColumn).Value = CmboApr.Value
                        .Cells(3, LastColumn).Value = "April"
                        .Range(.Cells(5, LastColumn), .Cells(Lastrow, LastColumn)).Value = Worksheets("Requirements").Range("I3:I" & Lastrow).Value
                    End With

I am trying to add to it so that it checks to see whether the data is already present. The criteria that it needs to check against are in the first 3 rows of each column. If these criteria are met then rows 5 to lastrow of that column are amended, however If these criteria are not met it then the data is added to the next available column.

I am using the following code which isnt quite working as the data is added to the next available column anyway even if the criteria are met, am I on the right lines??

VBA Code:
'Checks to see if the 3 top cells in the worksheet WSName1 contains the same data as in the CmboUnit, CmboApril Comboboxes and the 3rd cell has the work April in it
If Worksheets(WSName1).Cells(1, LastColumn).Value = CmboUnit.Value And _
                Worksheets(WSName1).Cells(2, LastColumn).Value = CmboApr.Value And _
                Worksheets(WSName1).Cells(3, LastColumn).Value = "April" Then
                Worksheets(WSName1).Range(.Cells(Column, Lastrow).Cells(Lastrow, LastColumn)).Value = Worksheets("Requirements").Range("I3:I" & Lastrow).Value
                Else
'if false add the data to a new column
With Worksheets(WSName1)
                        .Cells(1, LastColumn).Value = CmboUnit.Value
                        .Cells(2, LastColumn).Value = CmboApr.Value
                        .Cells(3, LastColumn).Value = "April"
                        .Range(.Cells(5, LastColumn), .Cells(Lastrow, LastColumn)).Value = Worksheets("Requirements").Range("I3:I" & Lastrow).Value
                    End With
                End If

Thanks

Steve
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,397
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
@stirlingmw1 The way I am interpreting that is that it is always putting the data into the next available column?
If so then your variable 'LastColumn' must be the actual last column + 1 ?
Try like Worksheets(WSName1).Cells(2, LastColumn -1 )......... in the expressions in your If statement.

Hope that helps.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,488
Messages
5,770,380
Members
425,613
Latest member
martinijr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top