CODEWITHSUNDEEP
excelvba
Niva
Niva

Reputation: 298

Updating cell value according to values from other column

friend of mine needs help with this code

    Sub autoUpdate()
    Dim R As String
    Dim A As String
    Dim G As String

    Dim myrange As Range
    Dim givenrng As Range

    Set myrange = Sheets("Sheet1").Cells(2, 2)
    Set givenrng = Sheets("Sheet1").Cells(2, 3)




    'If Worksheets("Project Details").Range("L") = R And Worksheets("Project Detials").Range("M") = R Then
    'Worksheets("Project Details").Range("K") = R

    If myrange.Value = "F" Then
    givenrng.Value = "B"
End sub

Instead of having myrange as a particular cell isit possible to change it to a column range? Means for example if any of the cells in column A is F then the corresponding row in column B will input value as B.

Upvotes: 1

Views: 1715

Answers (2)

Looker
Looker

Reputation: 144

Try this?

Dim lastrow as Long    
lastrow = Cells(Rows.Count, 2).End(xlUp).Row

For i = 1 to lastrow
  If Cells(i, 2).Value = "F" Then
      Cells(i, 3).Value = "B"
  End If
Next i

So from the first row of the sheet to last row of sheet containing data, if a cell in column B contains "F", column C will show "B" (since those are the columns that the cells from your friend's code are currently showing).

Although if it's specifically from column A to column B, you can just change the If to:

  If Cells(i, 1).Value = "F" Then
      Cells(i, 2).Value = "B"
  End If

EDIT: Although the above answer has been accepted, I came back and realized where the type mismatch error came from. I forgot to check that the column number on the line lastrow = Cells(Rows.Count, 2).End(xlUp).Row matched with the column that was being searched through.

Upvotes: 0

luke_t
luke_t

Reputation: 2975

You could use the .Find() Method, and then use the .Offset Property to enter a value into the cell 1 column to the right.

Option Explicit
Sub findValue()
    Dim rngCol As Range, rngFound As Range
    Set rngCol = Columns("A:A")

    Set rngFound = rngCol.Find(What:="F", LookIn:=xlValues)
    If Not rngFound Is Nothing Then
        rngFound.Offset(0, 1).Value = "B"
    End If
End Sub

This only finds the first string which matches the defined value, within the given range. If there are multiple matches within the range, you would need to incorporate this into a loop.

EDIT: Having read your question again, it sounds like there will be multiple values within the column. In this scenario it would probably be easier to enter your range into an array and then search the array for any matching values.

You can either input the value into the cell whilst looping through.

Sub arrayFindValue2()
    Dim rngValues() As Variant
    Dim lrow As Long, i As Long

    lrow = Cells(Rows.Count, 1).End(xlUp).Row
    rngValues = Range(Cells(1, 1), Cells(lrow, 2))
    For i = 1 To UBound(rngValues, 1)
        If rngValues(i, 1) = "F" Then
            Cells(i, 2).Value = "B"
        End If
    Next i
End Sub

On 60,480 rows of data, with 6048 matches, the above took 0.84 seconds to run. Even when turning off screen updating this only reduced to 0.80 seconds.

Or you can assign the value to the array and then populate the worksheet with the contents of the array at the end.

Sub arrayFindValue()
    Dim rngValues() As Variant
    Dim lrow As Long, i As Long

    lrow = Cells(Rows.Count, 1).End(xlUp).Row
    rngValues = Range(Cells(1, 1), Cells(lrow, 2))
    For i = 1 To UBound(rngValues, 1)
        If rngValues(i, 1) = "F" Then
            rngValues(i, 2) = "B"
        End If
    Next i
    Range("A1").Resize(UBound(rngValues, 1), 2).Value = rngValues
End Sub

Following the same test criteria, the above took 0.13 seconds to run.

EDIT: EXPLANATION

lrow = Cells(Rows.Count, 1).End(xlUp).Row This will find the last row in column A. Rows.Count finds out how many rows are on the worksheet, and the 1 represents column A. .End(xlUp) goes up in column A, from the bottom row which was found. By performing this we know the last row with data in, within column A.

Range(Cells(1, 1), Cells(lrow, 2)) We then use the last known row to create the array with values. Cells(1,1) represents Range("A1") and then Cells(lrow,2) will represent the last row which had data in, within column A, but assigning column B also to the array (so that we can populate the array with values to assign to column B). It effectively equates to Range("A1:B60480").

Upvotes: 1

Related Questions