Sanjhali has attended:
Excel VBA Introduction course
VBA
Hi . I have a data set Sheet name ("Info") where
Column 1 has name
column 2 has Group
Column 3 has class type (Example A, B , c, D)
Column 4 has country
Can you let me know how can i copy the entire row, if the value in class type is "C" into another tab named "Data".
Secondly, i have a "master country" sheet which has country assigned to each name. I now need to see if the country in column 4 is correct as per the “Master country” sheet. If not I need to enter the correct country and then highlight the values.
RE: VBA
Hi Sanjhali ,
Thank you for using the forum to ask a question.
Having attended the VBA Introduction course you will have covered some the tools to complete this task.
Can I check what code you've attempted to solve this problem?
That will help us know the knowledge level you are starting from so we can support with this question.
Kind regards
Richard
RE: VBA
Hi Richard. For my first query i am using this code but its not working after the for loop.
Sub ClassType()
Sheets("Info").Rows(1).Copy Sheets("Class C").Range("A1") 'copying first row of excel as it consistes of headers
Sheets("Info").Activate
Range("A1").Select
Dim a As Range
Dim Pc As Range
For Each cell In Range("C") ' this is selecting column C but i dont know how to make it dynamic
If Sheets("Clas C").Range("A2") = " " Then
Set Pc = Sheets("Class C").Range("A2")
Else
Set Pc = Sheets("Class C").Range("A1").End(xlDown).Offset(1, 0)
End If
If ActiveCell.Value = "C" Then ActiveCell.EntireRow.Copy Pc
Next cell
ActiveCell.Offset(1, 0).Select
Range("a1").Select
End Sub
RE: VBA
Hi Richard, please find below my code for the second query
Sub Country()
Rows(1).Find("Country").Select
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = Application.WorksheetFunction.VLookup(ActiveCell.Value, Sheets("Master Country").Range("A:B"), 2, False) Then
ActiveCell.Value = ActiveCell.Value
Else
ActiveCell.Value = Application.WorksheetFunction.VLookup(ActiveCell.Value, Sheets("Master Country").Range("A:B"), 2, False)
ActiveCell.Interior.Color = VBOrange
End If
Loop
End Sub
RE: VBA
Hello Sanjhali,
I have some suggested solution code for you. Please tell us if this doesn't work for you. Remember to adjust the references as needed.
1.
Sub Class_Type()
Sheets("Info").Columns("A:D").AutoFilter Field:=3, Criteria1:="C"
Sheets("Info").Range("a1").SpecialCells(xlCellTypeVisible).Cells.
Copy
Sheets("Class C").Activate
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Info").Range("A1").AutoFilter
End Sub
*This code uses a filter instead to find the Cs in the “Info” worksheet, then dynamically selects, copies and pastes the results into the “Class C” worksheet.
2.
Sub Country_Lookup()
Dim myRange As Range
Set myRange = Sheets("Master Country").Range("A1").CurrentRegion
Sheets("Info").Activate
Range("A2").Select
Do Until ActiveCell = ""
If ActiveCell.Offset(0, 3) <> WorksheetFunction.VLookup(ActiveCell, myRange, 4, 0) Then
ActiveCell.Offset(0, 3).Interior.Color = vbYellow
ActiveCell.Offset(0, 3) = WorksheetFunction.VLookup(ActiveCell, myRange, 4, 0)
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub
*This code uses a Vlookup to compare the countries in the “Info” sheet to those in the “Master Country” sheet. The cells with differences are shaded in yellow and the incorrect countries are replaced by the correct countries.
I hope this helps.
Kind regards
Marius Barnard
STL
