VBA

Resolved · Urgent Priority · Version 365

Thu 31 Mar 2022, 11:04 Delegate Sanjhali said...

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.

Thu 31 Mar 2022, 11:49 Trainer Richard said...

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

Thu 31 Mar 2022, 11:58 Delegate Sanjhali said...

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

Thu 31 Mar 2022, 12:08 Delegate Sanjhali said...

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

Tue 5 Apr 2022, 13:53 Trainer Marius said...

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

Training courses

	98.8 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Sum Up All the Values in A Column

If you want to quickly calculate the Summed values of all cells in a column in Excel 2003 normally you would use the SUM formula. (eg if you wanted to calculate the values in Column C rows 10 to 25) the formula would be:

=SUM(C10:C25)

However, if you keep adding values to column C you would keep having to modify the above SUM formula which can get quite annoying.

To get around this you can sum all the values in a column using the following formula:

=SUM(COLUMN:COLUMN)

Which, in our example, would be:

=SUM(C:C)

NOTE You cannot place this formula in column C, or else Excel 2003 will show a circular reference error.

The formula must be placed in any other column, EXCEPT the one being calculated.