Excel/VBA Code

Advertisement


Question
QUESTION: I have an excel spreadsheet that has collected data on it. The worksheet is 33 columns and 100005 rows.

I need a code to scan the data for 5 levels of criteria, highlight each cell and font to a certain color, Autofilter each row once each level has been identified and then copy and paste each levels results to its own separate worksheet within the workbook.

I have got pieces and parts of codes. I have code to identify level 1 conditions and highlight not sure how to copy and paste to another worksheet.

I also have code for level 2 conditions that works somewhat.

Can you please help?

Thank you for your time!

ANSWER: If it is code, it doesn't need to filter - it can simply identify the cells and set the value on another sheet to the value of those cells - you haven't said how much knowledge you have, or what code examples you have got but something like (pseudo code)

for each rar in myrange
if myrange=myconditions then
sheets("sheet2").range("a" & counter & ":ag" & counter).value=myrange.value
counter=counter+1
end if
next

would do it - as I say, this isn't strict code, it designed to show you the type of structure - but it assumes your data is in rows a to ag - happy to help further!

aidan.heritage@virgin.net

---------- FOLLOW-UP ----------

QUESTION: Hello Aidan,

Thank you for your response. I have been working with VBA in Excel for a month or so now. Below is the code I have so far for Level one conditions. I recorded a Macro to get this. I have not been able to figure out how to get it to select the data that meets these conditions and transfer it to the Sheet "Level 1". The Columns are B6:Q100005 and S6:AH100005.

Sub Level1()
'Scan Data Worksheet for level 1 conditions and weld conditions
'Level 1 = US thickness and MF signal are in acceptable range.


Dim MF As Integer
Dim US As Integer
Dim Rng As Range
   Set Rng = Application.Range("B:AH")

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'Test for Level 1 Conditions
If MF <= 25# And US >= 9# Then
   Answer = True
   
Else
   'Test for Welds
   If MF > 97.99 And US > 12.5 Then
       Answer = True
   Else
       Application.Goto Reference:="R6C2:R100005C17"
       Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
         Formula1:="=0.0000000000", Formula2:="=25.0000000000"
       Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
         With Selection.FormatConditions(1).Font
         .Bold = True: .Italic = False: .TintAndShade = 0
         End With
         With Selection.FormatConditions(1).Interior
         .Pattern = xlPatternLinearGradient: .Gradient.Degree = 90: .Gradient.ColorStops.Clear
         End With
         With Selection.FormatConditions(1).Interior.Gradient.ColorStops.Add(0)
         .ThemeColor = xlThemeColorDark1: .TintAndShade = 0
         End With
         With Selection.FormatConditions(1).Interior.Gradient.ColorStops.Add(0.5)
         .color = 10417306: .TintAndShade = 0
         End With
         With Selection.FormatConditions(1).Interior.Gradient.ColorStops.Add(1)
         .ThemeColor = xlThemeColorDark1: .TintAndShade = 0
         End With
         Selection.FormatConditions(1).StopIfTrue = False
       Application.Goto Reference:="R6C19:R100005C34"
       Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
         Formula1:="=9.0000000000", Formula2:="=15.0000000000"
       Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
         With Selection.FormatConditions(1).Font
         .Bold = True: .Italic = False: .TintAndShade = 0
         End With
         With Selection.FormatConditions(1).Interior
         .Pattern = xlPatternLinearGradient: .Gradient.Degree = 90: .Gradient.ColorStops.Clear
         End With
         With Selection.FormatConditions(1).Interior.Gradient.ColorStops.Add(0)
         .ThemeColor = xlThemeColorDark1: .TintAndShade = 0
         End With
         With Selection.FormatConditions(1).Interior.Gradient.ColorStops.Add(0.5)
         .color = 10417306: .TintAndShade = 0
         End With
         With Selection.FormatConditions(1).Interior.Gradient.ColorStops.Add(1)
         .ThemeColor = xlThemeColorDark1: .TintAndShade = 0
         End With
         Selection.FormatConditions(1).StopIfTrue = False
         Application.Goto Reference:="R1C1"
   End If 'MF > 97.99 And US > 12.5
 End If 'MF <= 25# And US >= 9#
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Answer
Can I see a sample worksheet - I cannot see where the

If MF <= 25# And US >= 9# Then
  Answer = True
  
Else
  'Test for Welds
  If MF > 97.99 And US > 12.5 Then
      Answer = True
  Else


gets set and it would appear as though this is the bit that matters?  My email is aidan.heritage@virgin.net - seeing the sheet would help to clarify - recorded macros are great but they have a lot of extra information!
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Aidan Heritage

Expertise

I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!

Experience

My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

Education/Credentials
I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

©2016 About.com. All rights reserved.