VBA Basics

[rguy84]

I am trying to get my head wrapped around basic concept but nothing I found helps.

In A1 i have the value 1
in B1 =if(a1=1,"yes","no)

When I press enter yes appears, duh. Can somebody show me what this would look like in VBA form?

 

[Desu Nota from Columbus]

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]=1,""yes"",""no"")"
    Range("B2").Select
End Sub

You know that you can record macros correct? I've learned countless functions like this just by doing simple 1 step recordings.

 

[Scott R]

I am trying to get my head wrapped around basic concept but nothing I found helps.

In A1 i have the value 1
in B1 =if(a1=1,"yes","no)

When I press enter yes appears, duh. Can somebody show me what this would look like in VBA form?

If Range("A1").Value = 1 then
 Range("B1").Value = "yes"
Else 
 Range("B1").Value = "no"
end if

 

[cjcobra]

Also, if you record a macro doing a lot of the things you want to learn, then you can go see what the code looks like.

 

[rguy84]

Thanks Scott. How would I make Range("A1") a variable and Range("B1") the current cell?

 

[rguy84]

Sub Macro1()
'
' Macro1 Macro
'
 
'
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]=1,""yes"",""no"")"
    Range("B2").Select
End Sub

You know that you can record macros correct? I've learned countless functions like this just by doing simple 1 step recordings.

Yes I know. But it looks like this only looks at b2. I dont want that. I want to know how to enter =myFunction(anyCell) and it processes

if(anyCell=1){
   currentCell.Value ="yes"
}else{ currentCell.Value="no"}

 

[Scott R]

Thanks Scott. How would I make Range("A1") a variable and Range("B1") the current cell?

Current cell can be referred to as ActiveCell in code.

Not sure if you want Range("A1") to be assigned to a variable, or if you want the range itself to vary... What are you wanting to do?

 

[rguy84]

Scott, This was just me trying to learn. I just wanted to understand how if i can click in any cell (say b99) and see if any other cell (that I specify) equals 1. If it does put "yes" in that cell (b99) for this sample if not put "no".

 

[Desu Nota from Columbus]

Scott, This was just me trying to learn. I just wanted to understand how if i can click in any cell (say b99) and see if any other cell (that I specify) equals 1. If it does put "yes" in that cell (b99) for this sample if not put "no".

So you want it to say instead of Range("A1").Value = 1, you want to know how to select a broader range like all of column A?

If Range("A:A").Value = 1 then  
 Range("B:B").Value = "yes" 
Else   
 Range("B:B").Value = "no" 
end if

This would apply to all cells in column B (based on presence of "1" in corresponding A cell)

is this what you are wanting to know?

 

[Desu Nota from Columbus]

So you want it to say instead of Range("A1").Value = 1, you want to know how to select a broader range like all of column A?

If Range("A:A").Value = 1 then  
 Range("B:B").Value = "yes" 
Else   
 Range("B:B").Value = "no" 
end if

This would apply to all cells in column B (based on presence of "1" in corresponding A cell)

is this what you are wanting to know?

Getting more involved, if you want to apply your condition to only cells that are occupied in Column A (so skip over blank cells) you would use the following:

sub macro1()

ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",IF(RC[-1]=1,""yes"",""no""))"
    Range("B1").Select
    Selection.Copy
    Columns("B:B").Select
    ActiveSheet.Paste
Application.CutCopyMode = False

end sub