For i = 2 To N

MsgBox Cells(i, "A")

Next msgbox will show contents of cell A1, A2, A3 next causes increment of i

TO ASSIGN VARIABLE VALUE TO A CELL

Dim i As Long, j As Long, k As Long

Dim branchcount As Integer

branchcount = j

Cells(i, "B").Value = branchcount

Row number or column number tells macro when to stop

Cells(i, k).Activate to see the location of cell in process

Cells(i, "N").Value = Cells(i, "J").Value & " " & Cells(i, "K").Value join two
columns

Dim newSheet As Worksheet YOU HAVE TO DEFINE WORKSHEET

Range("A1").CopyRange("C1")COPYCOMMANDcopiescontentsofcellA1intocellC1
Range("A1:A3").CopyRange("D1:D3")copiescontentsofcellsA1,A2,A3intocellDA1,D2,D3
Range("A1:A3").CopyRange("D1")

Cells(i, "A")= cells (row, column)

Cells(i, k + 1).Activate increment variable in cell increment cell value to next cell using variable

Range(Cells(j, "G"), Cells(j, "H")).Copy use "H" for fixed column or "1" for fixed row

Cells(i, LC)) has use cell with two variables defined as long Dim N As Long, i As Long, j As Long, LC As Long

AVOID USING COLUMN NAMES AS VARIABLE LIKE i

Count number of filled columns in row i LC = Cells(i, Columns.Count).End(xlToLeft).Column

IsEmpty(Cells(i, "A")) = False to check cell in col A and Row i is blank or not

Count number of filled rows in column A Dim N As Long

N = Cells(Rows.Count, "A").End(xlUp).Row

N = Cells(1, Columns.Count).End(xlToLeft).Column

' N counts number of filled columns in row 1 worksheet.

If IsEmpty(Cells(i, "A")) = False checks the contents in column A row i

Rows(i & ":" & i).Select selects the row i

TO SELECT FILLED CELLS IN A ROW ADD COLOUR TO CELL OR RANGE OR to select two cells to select range

Range(Cells(i, 1), Cells(i, LC)).Interior.ColorIndex = 15

' MARKS filled cells in the row i from I1 to LC. AND ADD COLOUR TO THEM WITH INDEX 15

ActiveSheet.Range(Cells(i, 1), Cells(i, LC)).Select

' SELECTS filled cells in the row.

TO PASTE DATA IN BLANK CELLS OF THE ROW 1 FIRST ROW

Range("1:1").Find("").Select to select blank cell in row 1

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False TO vba to paste in active cell above

Sub test()

k = Cells(Rows.count, "A").End(xlUp).Row

MsgBox (k)

End Sub

MESSAGEBOX GIVES RESULT value of k here it counts number of filled rows in column A

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SELECT A CELL USING VARIABLE

How would I select a cell based on a variable beginPosition containing the row number. For example if my beginPosition
variable is 10, how would I select cell A10?

Range("A" & beginPosition).Select

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SELECT CELL USING VARIALE Dim var As Integer

Cells(1, var).Select

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

IF CELL CONTAINS TEXT

For var = 2 To N

MsgBox var

Cells(1, var).Select

If Cells(1, var).Value = "TOTAL" Then

MsgBox "move to next" loop if cell with vaiable contains text TOTAL

Else

End If

Next var STARTS THE LOOP

Xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

To SELECT CELL BELOW TO SELECT CELL USING VARIABLE

Cells(1, var).Select CELLS REFERRED IN ROW 1 IN ALL COLUMNS GIVEN BY VARIABLE var

If Cells(1, var).Value = "TOTAL" Then IF USED WITH VARIABLE var TOCHECK IF IT CONTAINS TOTAL
 ActiveCell.Offset(1, 0).Select TO SELECT CELL BELOW USE OFFSET

)=IFERROR(INDEX($A$2:$A$835, MATCH(0, COUNTIF($B$1:B823, $A$2:$A$835), 0)),"")

Range A2:A835 contains duplicate values,
an array formula in B2:B16 extracts an unique distinct list
from column range A1:A835.
You can copy below formula to B2,
and press CTRL+SHIFT+ENTER,
then drop down OR COPY CELLS TO B2 to B835.

Xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

link worksheet to Nother workbook

Thanks for the help! Everything has solved itself when I used different approach in referring to workbooks. It looks strange but it
seems to work fine:

Set wsNewSheet = Workbooks.Open("C:\Minestar_exports\" & Pts & "")

Set wsO_Sheet = Workbooks.Open("" & OldBookName & "")

I figured that I didn't have to refer to a specific worksheet if I'm only interested in the default one and it works great now!

Xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

with sheets("Sheet1")
.Range(.Cells(2, 3), .Cells(10, 3)).Copy _
Destination:=ws2.Range(ws2.Cells(3,4))
end with

The prefixing period (aka . or full stop) means that the parent of .Range and .Cells is defined with the With...End With
statement. To shorten the code necessary to properly define the range on Sheet2, I assigned a worksheet type variable and
used it to show the parent of both the range and the cells.

TO CHECK WHETHER MACRO IS RUNNING IN THE CURRENT WORKBOOK

MsgBox ThisWorkbook.Path & vbNewLine & ThisWorkbook.Name just above theWith Sheets("Data_Sheet") and verify if the
macro is running from the correct workbook?

Syntax to find syntax to search

Dim FindRow as Range

Set FindRow = Range("A:A").Find(What:="ProjTemp", _' This is what you are searching for
After:=.Cells(.Cells.Count), _ ' This is saying after the last cell in the_
' column i.e. the first
LookIn:=xlValues, _ ' this says look in the values of the cell not the formula
LookAt:=xlWhole, _ ' This look s for EXACT ENTIRE MATCH
SearchOrder:=xlByRows, _ 'This look down the column row by row
'Larger Ranges with multiple columns can be set to
' look column by column then down
MatchCase:=False) ' this says that the search is not case sensitive
How to find row number of cell within a range : how to use text as variable : use content of cell as variable

As cell changes with cell i the value of variable (Collegename) changes with it

Nested variables or nested search

How to find cell value: how to get row number:

to find or search in another sheet assign the contents of the cell to a variable in sheet1 and select sheet2 before you find or
search

Dim Collegename As Variant

Collegename = Cells(i, "A")

MsgBox Collegename

shb.Select

Set FindRow = Range("B:B").Find(What:=Collegename, LookIn:=xlValues)

j = FindRow.Row

MsgBox j

' j gives row number of row having text reffered by variable=Collegename.

how do you tell vba to go back up to an earlier line in your code GOTO any line
goto back go up goback use line1:
Sub gotoExample()
Dim a As Integer
a=1
line1:
a=a+1
If a < 10 Then
GoTo line1
End If
End Sub

Double loop using two FOR select entire used rsnge

select all cells
You can use a double loop to loop through a two-dimensional range of cells.

Place a command button on your worksheet and add the following code lines:

Dim i As Integer, j As Integer

For i = 1 To 6
For j = 1 To 2
Cells(i, j).Value = 100
Next j
Next i
If you want to select from A1 to the end of the used range, you can use the SpecialCells method like this

With ActiveSheet
.Range(.Cells(1, 1), .Cells.SpecialCells(xlCellTypeLastCell)).Select
End With

Double loop using two FOR select entire used rsnge

select all cells
You can use a double loop to loop through a two-dimensional range of cells.

Place a command button on your worksheet and add the following code lines:

Dim i As Integer, j As Integer

For i = 1 To 6
For j = 1 To 2
Cells(i, j).Value = 100
Next j
Next i

If then else if then endif

The macro starts at cell A2 THE CHECKS CELL A3 OR A4 for data the accordingly
fills cell A2 WITH YES OR NO
If Cells(i, j + 1).Value = "" Then IF CELL A3 IS BLANK THEN CHECK A4
IS BLANK
If Cells(i, j + 2).Value = IF A3 IS NOT BLANK THEN EXECUTE $2
"" Then
Cells(i, j) = "NO" IF A3 IS BLANK CHECK A4 IF A4 IS
BLANK . IF BOTH BLANK FILL A2 WITH
NO AND INCREMENT j
j=j+3 IF A3 IS BLANK CHECK A4 IF A4 IS NOT
BLANK THEN EXECUTE $1
ELSE.............
$1.................... ENDIF
ELSE...................$2.................. ENDIF

NESTED FOR LOOP FILL ALL ROWS FILL ALL COLUMNS USIMNG TWO VARIABLES
Sub addtext1()

' addtext Macro

' the macro fills yes or no if there is data nin nest two cells or not

Dim i As Long, j As Long

Sheets("Sheet1").Select
R = Cells(Rows.Count, "A").End(xlUp).Row
' R counts number of filled rows worksheet.
 C = Cells(1, Columns.Count).End(xlToLeft).Column
' Ccounts number of filled columns in row 1 worksheet

For i = 2 To R
For j = 2 To C - 3

If Cells(i, j + 1).Value = "" Then

If Cells(i, j + 2).Value = "" Then
Cells(i, j) = "NO"
j=j+3

Else
Cells(i, j).Value = "YES"
j=j+3
End If

Else
Cells(i, j).Value = "YES"
j=j+3

End If

Next j
Next i

End Sub

Add blank row insert row NOP no opearation with if

Extend the for loop till end R = R + 200 by adding 200

Very important to activate cell so that it move to next column ele it will use the
macro in samerow
R = Cells(Rows.Count, "E").End(xlUp).Row
' R counts number of filled rows worksheet.
MsgBox R
R = R + 200
MsgBox R
For i = 3 To R
Cells(i, "C").Activate
If Cells(i, "C").Value = "" Then
MsgBox i
Else
ActiveCell.EntireRow.Insert
i=i+1
End If

Next i
End Sub

To count number of filled rows using if then and go to

Sub numofbranchesincollege()

' stop of Blank row based on FILLED E COLUMN. SHEET NAMED DATA GETS MODIFIED
' COLUMN B HAS ENGG COLLEGE NAMES WITH BLANKS SO MACRO TESTS BLANK IN COLUMN C
' insert new blank row based on COLUMN B
'1000 incremented in R so that all even if 200 rows are added with the macro even then the loop continues till end
Dim i As Long, j As Long, k As Long
Sheets("branchcount").Select

For i = 1 To 2000
k=i
MsgBox i
j=0

line1:

If Cells(k, "A").Value = "" Then

Else
j=j+1
k=k+1
GoTo line1

End If
MsgBox j

i=k
Next i

End Sub