CODEWITHSUNDEEP
vbaexcel
sreekanth
sreekanth

Reputation: 23

Inserting text to blank row using vba macro in excel

I have a data of say more than 5000 rows and 10 columns. I would like to add a text to the rows based on columns conditions.

    A              B               C            D
   fname          lname         state        clustername
1.  ram           giri            NCE         ...  
2. philips        sohia           MAD         ...
3. harish        Gabari           NCE         ....

Based on the column state, for NCE the cluster name is "nce.net" has to be assigned to column D (clustername) and also for MAD is "muc.net" to be assigned to row 2.

could you please help me out.

Here is my code:

dim emptyrow as string
row_number = 1
lastRow = Cells(Rows.Count, "D").End(xlUp).Row
state = sheets("sheet1").rows("C" & row_number)
for each cell in selection
 if instr(state, "NCE") = true then
    Range(Cells(emptyrow, "D").Value = Array("nce.net")
 end if
next emptyrow

Could you please help me out.

Upvotes: 1

Views: 1251

Answers (2)

MikeD
MikeD

Reputation: 8941

You may create a reference table consisting of unique state and clustername in a seperate worksheet and then pull the clustername into your original sheet using a =VLOOKUP() function ... provided there is a 1:1 relation between state and cluster ... even 1 cluster for multiple states would work. This way you avoid hardcoding and you can react quickly if cluster names change.

Example:

in Sheet2 list all countries and their associated clusternames in Sheet1 enter =VLOOKUP(...) into first row of clustername column as per below picture and copy down for all rows

enter image description here

Of course you may want to have values only, not formulas in your cluster column; then you can convert formulas into values by copying and then pasting as values that cluster column after you've entered the =VLOOKUP(...) formula.

Alternatively, if e.g. you have a lot of clusternames already defined and only want to work on rows where clustername is blank, you can

  1. filter for blank clusternames and insert the =VLOOKUP(...) only there

  2. use a small piece of code

    Sub DoCluster()
Dim R As Range, S As Integer, C As Integer, Idx As Integer

Set R = ActiveSheet.[A2] ' top left cell of table
S = 3                    ' column index of State column
C = 4                    ' column index of Clustername column
Idx = 1                  ' start at 1st row within range

' run a loop across all rows, stop if 1st column gets blank
Do While R(Idx, 1) <> ""

    ' work only on rows wher cluster not yet set
    If R(Idx, C) = "" Then
        ' now this isn't really good ... try to avoid hardcoding BY ANY MEANS
        Select Case R(Idx, 3)

        Case "NCE"
            R(Idx, 4) = "nce.net"

        Case "MAD"
            R(Idx, 4) = "muc.net"

        ' insert other cases here as per need
        ' ...

        ' trap undefined cases
        Case Else
            R(Idx, 4) = "undefined"
        End Select
    End If

    Idx = Idx + 1
Loop
End Sub

Personally I don't like this kind of hardcoding at all, I'd rather take the clusternames from a table ... so for me there wouldn't be a need to write code unless the whole task is much more complex than described.

Upvotes: 1

brettdj
brettdj

Reputation: 55672

Why not a simple formula

In D1 and copy down

=IF(C1="NCE","nce.net",IF(C1="MAD","muc.net","No match"))

Doing the same this with code

Sub Simple()
Dim rng1 As Range
Set rng1 = Range([c1], Cells(Rows.Count, "C").End(xlUp))
With rng1.Offset(0, 1)
    .FormulaR1C1 = "=IF(RC[-1]=""NCE"",""nce.net"",IF(RC[-1]=""MAD"",""muc.net"",""No match""))"
    .Value = .Value
End With
End Sub

Upvotes: 1

Related Questions