Test data against Conditions

E

erock24

Well-known Member
Joined
Oct 26, 2006
Messages
1,163
I have a large set of data - 15000 lines

I would like to set up a macro to cycle through the data and test my conditions.
Example of conditions

if B = 405 then make G ="HQ"
If n = 123456 then make G ="Loc"
ect.

I have about 21 conditions I would like to add.

thank you
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
I was able to paste this code together and it seems to be solving my issue

thanks,.

Code: 
Sub organize()
 Dim LastRow As Long, i As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = LastRow To 419 Step -1

'conditons

Next i
End Sub
 
Upvote 0
How can I change the following condition to say "if the last value in N = 6 Then......
Note N can be a 7 digit number and it can be formatted as number or text.

Code: 
If Range("N" & i).Value = "467" Then Range("BI" & i).Value = "AR"

thank you.
 
Upvote 0
Hi,

To me, it isn't clear what you want. If N is a 7 digit number, I don't think the format makes any difference: however it would be an issue if column N contains text. So, text 467 instead of number 467. One approach is something like this spreadsheet formula "6=(N2/10-INT(N2/10))*10" - it'll be pretty similar in VBA. If it is text, then something like 6=RIGHT(N2,1)

There are better alternatives to looping through the 15,000 rows and checking 21 conditions, changing cells as you go. Looping through the worksheet cells will be slow and is best avoided.

A fairly fast option would be to instead load the data into a (~15,000 rows x how ever many columns) array, and loop through updating the array in VBA and then pasting the modified array back onto the worksheet. Although this still uses looping, it should be adequately fast.

Though, I'd recommend another approach, that does not use looping. Using an SQL UPDATE like the sample below. I have assumed the data range has a name "MyData" and a header row with field names. I have late bound.

Code: 
Sub untested()

  Dim objConnection As Object

  Set objConnection = CreateObject("ADODB.Connection")

  With objConnection

    .Open Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _
        ThisWorkbook.FullName, "Extended Properties=""Excel 8.0;"""), vbNullString)

    'I have assumed named range "MyData" for the data table containing headers
    'Change to YOUR field names such as MyData.YourFieldName
    .Execute "UPDATE MyData SET MyData.ColGFieldName = 'HQ' WHERE MyData.ColBFieldName = 405"
    .Execute "UPDATE MyData SET MyData.ColGFieldName = 'Loc' WHERE MyData.ColNFieldName = 123456"
    ' ... and similar for balance of 21 conditions ...

    .Close
  End With

  Set objConnection = Nothing

End Sub
 
Upvote 0
Fazza,
thank you for your reply and the code you supplied. Great advice for future applications.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
How do you calculate a conditional sum for conditional formatting?
Replies
9
Views
287
Peter_SSs
Peter_SSs
G
  • Question
Get VBA to automatically generate an email when a cell meets a date condition
Replies
3
Views
225
Logit
Logit
J
  • Question
Conditional formatting based on value of another cell
Replies
3
Views
277
etaf
etaf
E
  • Question
Basic Gantt Chart - how to use conditional formatting to highlight percentage allocation per project
2
Replies
12
Views
474
awoohaw
awoohaw
L
  • Question
Add parts of row to table
Replies
3
Views
224
NoSparks
N

Forum statistics

Threads
1,215,339
Messages
6,124,381
Members
449,155
Latest member
ravioli44

Share this page

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
Back
Top