asked on

Need a Macro to fill the empty cells based on conditions - Excel 2010

I am looking for a Macro which will help me to fill the empty cells based on conditions.

Condition 1: If the ID starts with ‘IN’ and the country column is blank the then the MACRO should fill the country column as India. Likewise, if the ID starts with ‘US’ then country ‘United States’, if ID start with JP the country as ‘Japan’.

If the ID start in any other format do nothing..

Condition 2: If the cell in ‘Assigned’ column is blank then fill the column with the value in same raw from column ‘Team’.

For example in the sample excel file, Cell C4 & C6 is empty, so they should be replaced with E4 & E6 respectively.

Many Thanks in advance...
subsun.xlsx

Saqib Husain

Try this macro. You will have to select the entire table then run the macro

Sub fillblanks()
Dim cel As Range
For Each cel In Selection.Cells
If cel.Value = "" Then
Select Case cel.Column
Case 3
cel.Value = cel.Offset(0, 2).Value
Case 4
Select Case Left(cel.Offset(0, -3), 2)
Case "IN"
cel.Value = "India"
Case "US"
cel.Value = "United States"
Case "JP"
cel.Value = "Japan"
End Select
End Select
End If
Next cel
End Sub

SubSun

ASKER

That was quick and outstanding!.. It’s working fine..

Once more question.. The number of rows in data may vary so is there any way to select the data automatically and run the MACRO? I have some other MACROS to prepare a consolidated report. If you can help me with the code for auto selection for this sheet then I can fully automate the report.. ;-)

ASKER CERTIFIED SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SubSun

ASKER

Perfect!