Excel VBA – How to Execute Function in Excel

microsoft excelmicrosoft-excel-2007microsoft-excel-2010vbaworksheet-function

I'm trying to get this function in VBA but I'm new to it. I don't seem to know how I can get this done:

What I'm trying to do is, for each cell in column B that's numeric, I want to take the next two cells in column H i.e if it's B7, I'd like to take H8 and H9 (next two row values in H column) and concatenate them (they're strings) and save them in I7 (same row as B7) and finally delete the values in H8,H9.

Here's where I got to, I know it isn't much.

Private Sub CommandButton1_Click()
Dim B As Range, H As Range, I As Range
For Each B In ClientTable.Columns(2).Cells
    If IsNumeric(B) Then
    (What should be done here?)    
    End If
Next B
End Sub

Best Answer

I found IsNumeric(B) to be true for blank cells, so I changed your If statement accordingly. This worked for me:

    If IsNumeric(B) And B <> "" Then
        Cells(B.Row, 9) = Cells(B.Row + 1, 8) & Cells(B.Row + 2, 8)
        Cells(B.Row + 1, 8) = ""
        Cells(B.Row + 2, 8) = ""
    End If
    If B.Row > 9 Then Exit For

Obviously 8 and 9 are the column numbers for H and I, respectively. I added the

If B.Row > 9 Then Exit For

line because I had a problem with the loop not terminating; if

For Each B In ClientTable.Columns(2).Cells

works for you, you can take out the If B.Row > 9 Then Exit For.

Related Solutions

Excel VBA – OnClick Button Functionality

There are a few problems with your example code:

sheet.name.Cells() = "Adam" OR "Edward"

you need to check one cell at a time, rather than Cells()
to refer to the value of the cell, you need to refer to the cell Value property, ie sheet.range().value = "Adam"
each comparison (Adam or Edward) needs to be evaluated separately, ie range().value = "Adam" OR range().value = "Edward"

newsht.Rows.Value(code,title,date,name,description,status)

Value doesn't take named ranges like this
this code doesn't use the iterator i to say which line to copy the data to in sheet2

Try the code below. You can assign it to a button if you wish. You'll need to change the range dat and newdat to suit your sheet. The main change from your code is that offset is used to iterate through the various rows and columns (offset from the upper left corner of the data block) to check each line of data. Each check or copy is coded explicitly.

Sub macro2()

Set sht = ThisWorkbook.Worksheets("Sheet1")
Set newsht = ThisWorkbook.Worksheets("Sheet2")
'Set dat = sht.Range("p9")
Set dat = sht.Range("code").Cells(1, 1)
Set newdat = newsht.Range("c2")

'initialise counters
i = 1
j = 1

'set headings on sheet 2
newdat.Offset(0, 0).Value = dat.Offset(0, 0).Value 'copy code
newdat.Offset(0, 1).Value = dat.Offset(0, 2).Value 'copy title
newdat.Offset(0, 2).Value = dat.Offset(0, 3).Value 'copy date
newdat.Offset(0, 3).Value = dat.Offset(0, 4).Value 'copy name
newdat.Offset(0, 4).Value = dat.Offset(0, 5).Value 'copy descr
newdat.Offset(0, 5).Value = dat.Offset(0, 6).Value 'copy status



Do While dat.Offset(i, 0).Value <> "" 'loop till code data goes blank
  If ((dat.Offset(i, 4).Value = "Adam" Or dat.Offset(i, 4).Value = "Edward") And dat.Offset(i, 6).Value = "active") Then 'check conditions
    newdat.Offset(j, 0).Value = dat.Offset(i, 0).Value 'copy code
    newdat.Offset(j, 1).Value = dat.Offset(i, 2).Value 'copy title
    newdat.Offset(j, 2).Value = dat.Offset(i, 3).Value 'copy date
    newdat.Offset(j, 3).Value = dat.Offset(i, 4).Value 'copy name
    newdat.Offset(j, 4).Value = dat.Offset(i, 5).Value 'copy descr
    newdat.Offset(j, 5).Value = dat.Offset(i, 6).Value 'copy status
    j = j + 1
  End If

  i = i + 1
Loop


End Sub

Best Answer

Related Solutions

Excel VBA – OnClick Button Functionality

Related Question