Accepted answer

first, if tacticinput = "<>" doesn't test if the cell is not blank. you should use isempty instead:

if not isempty(tacticinput.offset(0, i)) then

also, when you perform the offset, i would expect that you would get a runtime error. you want a set there as well so that you are changing which range is being referred to.

if tacticinput = "<>" then
    set tacticinput = tacticinput.offset(0, 2)
    set dateinput = dateinput.offset(0, 2)



dim i as long
truevalue = false
i = 0

do until truevalue = true
    if tacticinput.offset(0, i) is nothing then
        tacticinput.offset(0, i) = codetextbox.value
        dateinput.offset(0, i) = datebox.value
        truevalue = true
        i = i + 2
    end if


you wouldn't need a loop for this necessarily. instead, you could use find to get the last available empty cell in that range, then use that to define the cell the values should be written to. example:

sub test()

    dim rinput as range
    dim lastcol as long

    set rinput = sheets("sheet1").range([b2], cells(rows.count, "al")).specialcells(xlcelltypevisible)
    lastcol = rinput.rows(1).cells.find("*", lookin:=xlvalues, searchorder:=xlbycolumns, searchdirection:=xlprevious).column

    if lastcol mod 2 = 0 then
        lastcol = lastcol + 2
        lastcol = lastcol + 1
    end if

    cells(rinput.cells(1, 1).row, lastcol).value = codetextbox.value
    cells(rinput.cells(1, 1).row, (lastcol + 1)).value = datebox.value

end sub

let me know if you need clarification.


your problem is that you wrote if tacticinput = "<>" then: in this case tacticinput is probably never equal to a string in which the followin characters <> are located. you may have wanted to write if tacticinput = "" which is the very contrary of if tacticinput <> ""

you could try a very simple loop:

i = 0

do until isempty(yourrange.offset(i,0))
    i = i + 2

yourrange.offset(i,0) = "write what you want here"

Related Query

More Query from same tag