CODEWITHSUNDEEP
excelvba
Kaan Çağhan
Kaan Çağhan

Reputation: 93

How to change range of values with another range of values in Excel VBA

I am trying to change range of values with another range of values in VBA. I tried following code but i got error: Type missmatch

Sub ChangeOneToGrade()
Dim rng As Range
Set rng = Application.InputBox(Prompt:="Please select a range", Type:=8)
Dim rng2 As Range
Set rng2 = rng
Dim Count As Integer
For Each cell In rng.Rows
 If cell.Value = 1 Then
 cell.Value = rng2.Offset(0, Count)
 End If
Count = Count + 1
Next cell
End Sub

Upvotes: 1

Views: 1627

Answers (2)

JMP
JMP

Reputation: 4467

I think this is what you are trying to achieve - replace every occurrence of 1 in rng with the corresponding value in rng2. I have added some default data, and default ranges to the InputBoxs.

Dim i As Integer
For i = 1 To 7
    Cells(1, i) = Int(Rnd * 2)
    Cells(2, i) = i + 1
Next i
Dim rng As Range
Set rng = Application.InputBox(Prompt:="Please select a range", Default:="a1:g1", Type:=8)
Dim rng2 As Range
Set rng2 = Application.InputBox(Prompt:="Please select a range", Default:="a2:g2", Type:=8)
Dim Count As Integer: Count = 1
For Each cell In rng
    If cell.Value = 1 Then
        cell.Value = rng2.Columns(Count)
    End If
    Count = Count + 1
Next cell

Your 'Type mismatch' comes from rng.Rows returns a collection of rows, and so cell was iterating through rows and not cells, and so cell.Value had no meaning.

Upvotes: 1

klausnrooster
klausnrooster

Reputation: 560

This isn't ideal, but it should get you on the right track. Cells(Row, Column) is used. You can swap Row & Column, see what happens.

Sub ChangeOneToGrade()
Dim rng1 As Range
Set rng1 = Application.InputBox(Prompt:="Please select a range", Type:=8)
Dim rng2 As Range
Set rng2 = Application.InputBox(Prompt:="Select another range", Type:=8)

Dim cnt As Long: cnt = 1
Dim celll As Range  'extra l on purpose
For Each celll In rng1
    'Do 1 IF with "And" and you'll get same Type-Mismatch
    If IsNumeric(celll.Value) Then
        If celll.Value = 1 Then
            rng2.Cells(cnt, 1).Value = 1
            cnt = cnt + 1
        End If
    End If
Next
End Sub

Upvotes: 2

Related Questions